Re: How to interpret this explain analyse? - Mailing list pgsql-performance

Hi Tom,

Tom Lane schreef:
> On the surface this looks like a reasonable plan choice.  If you like
> you can try the other two basic types of join plan by turning off
> enable_hashjoin, which will likely drive the planner to use a merge
> join, and then also turn off enable_mergejoin to get a nested loop
> (or if it thinks nested loop is second best, turn off enable_nestloop
> to see the behavior with a merge join).

The problem is that the query logically requests all records  ( as in "select * from a join") from the database but
actuallydisplays (in practise) in 97% of the time the first 1000 records and at most the first 50.000 records
99.99999999999999%of the time by scrolling (using "page down) in the gui and an occasional "jump to record xxxx"
throughsomething called a locator) (both percentages tested!). 

If I do the same query with a "limit 60.000" or if I do a "set enable_seqscan = off" the query returns in 0.3 secs.
Otherwiseit lasts for 20 secs (which is too much for the user to wait for, given the circumstances). 

I cannot change the query (it is geneated by a tool called Clarion) but it something like (from the psqlodbc_xxx.log):
"...
declare SQL_CUR01 cursor for
SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer ORDER BY
A.klantnummer;
fetch 100 in SQL_CUR01;
..."

PostgreSQL does the planning (and than executes accordingly) to the query and not the "fetch 100". Changing the query
witha "limit whatever" prohibits scrolling after the size of the resultset. If Postgres should delay the planning of
theactual query untill the fetch it could choose the quick solution. Another solution would be to "advise" PostgreSQL
whichindex etc (whatever etc means ;-))  to use ( as in the mailing from Silke Trissl in the performance list on
09-02-05).

> What's important in comparing different plan alternatives is the ratios
> of estimated costs to actual elapsed times.  If the planner is doing its
> job well, those ratios should be similar across all the alternatives
> (which implies of course that the cheapest-estimate plan is also the
> cheapest in reality).  If not, it may be appropriate to fool with the
> planner's cost estimate parameters to try to line up estimates and
> reality a bit better.
I I really do a "select *" and display the result, the planner is right (tested with "set enable_seqscan = off" and
"setenable_seqscan = on). 

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl