Re: tuning seqscan costs

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)