tuning seqscan costs

From: Katherine Stoovs <ambrosiac(at)nedsenta(dot)nl>
To: pgsql-hackers(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: tuning seqscan costs
Date: 2005-10-19 14:51:55
Message-ID: dj5gjc$q0g$1@news.host.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Matthew T. O'Connor 2005-10-19 15:01:09 Re: Segv in pg_autovacuum in 8.0.x
Previous Message Lyubomir Rusanov 2005-10-19 14:08:08 Re: [HACKERS] Call for translators

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2005-10-19 14:54:21 Re: Inefficient escape codes.
Previous Message Peter Childs 2005-10-19 07:19:04 Re: Inefficient escape codes.