pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtuning seqscan costs
3+ messages / 3 participants
[nested] [flat]
* tuning seqscan costs
@ 2005-10-19 14:51 Katherine Stoovs <ambrosiac@nedsenta.nl>
0 siblings, 2 replies; 3+ messages in thread
From: Katherine Stoovs @ 2005-10-19 14:51 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [HACKERS] tuning seqscan costs
@ 2005-10-20 14:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Katherine Stoovs <ambrosiac@nedsenta.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-10-20 14:15 UTC (permalink / raw)
To: Katherine Stoovs <ambrosiac@nedsenta.nl>; +Cc: pgsql-hackers@postgresql.org; pgsql-performance
Katherine Stoovs <ambrosiac@nedsenta.nl> writes:
> 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.
Instead of handwaving, how about showing us EXPLAIN ANALYZE results for
both cases? You didn't even explain how the index you expect it to use
is defined...
Specifying what PG version you are using is also minimum required
information for this sort of question.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: tuning seqscan costs
@ 2005-10-26 22:48 Thomas F. O'Connell <tfo@sitening.com>
parent: Katherine Stoovs <ambrosiac@nedsenta.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas F. O'Connell @ 2005-10-26 22:48 UTC (permalink / raw)
To: Katherine Stoovs <ambrosiac@nedsenta.nl>; +Cc: pgsql-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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-26 22:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-19 14:51 tuning seqscan costs Katherine Stoovs <ambrosiac@nedsenta.nl>
2005-10-20 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 22:48 ` Thomas F. O'Connell <tfo@sitening.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox