Skip site navigation (1) Skip section navigation (2)

Re: tuning seqscan costs

From: "Thomas F(dot) O'Connell" <tfo(at)sitening(dot)com>
To: Katherine Stoovs <ambrosiac(at)nedsenta(dot)nl>
Cc: PgSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: tuning seqscan costs
Date: 2005-10-26 22:48:17
Message-ID: 48454B5A-D7D5-4387-B00F-CF5D1B9916EC@sitening.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Oct 19, 2005, at 9:51 AM, Katherine Stoovs wrote:

> I want to correlate two index rows of different tables to find an
> offset so that
>
> table1.value = table2.value AND table1.id = table2.id + offset
>
> is true for a maximum number of rows.
>
> To achieve this, I have the two tables and a table with possible
> offset values and execute a query:
>
> SELECT value,(SELECT COUNT(*) FROM table1,table2
>                               WHERE table1.value = table2.value AND
>                                     table1.id = table2.id + offset)
>              AS matches FROM offsets ORDER BY matches;
>
> The query is very inefficient, however, because the planner doesn't
> use my indexes and executes seqscans instead. I can get it to execute
> fast by setting ENABLE_SEQSCAN to OFF, but I have read this will make
> the performance bad on other query types so I want to know how to
> tweak the planner costs or possibly other stats so the planner will
> plan the query correctly and use index scans. There must be something
> wrong in the planning parameters after all if a plan that is slower by
> a factor of tens or hundreds becomes estimated better than the fast
> variant.
>
> I have already issued ANALYZE commands on the tables.
>
> Thanks for your help,
> Katherine Stoovs

Katherine,

If offset is a column in offsets, can you add an index on the  
expresion table2.id + offset?

http://www.postgresql.org/docs/8.0/static/indexes-expressional.html

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Open Source Solutions. Optimized Web Development.

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-10-26 23:06:15
Subject: Re: Materializing a sequential scan
Previous:From: PFCDate: 2005-10-26 21:49:54
Subject: Re: browsing table with 2 million records

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-10-26 23:06:19
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
Previous:From: Tom LaneDate: 2005-10-26 22:38:45
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group