Re: Retry: Is this possible / slow performance? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Retry: Is this possible / slow performance?
Date
Msg-id 27755.1107795836@sss.pgh.pa.us
Whole thread Raw
In response to Retry: Is this possible / slow performance?  ("Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl>)
Responses Re: Retry: Is this possible / slow performance?  (PFC <lists@boutiquenumerique.com>)
List pgsql-performance
"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> Two queries: one with "set enable_seqscan = on" , the other with "set enable_seqscan = off". The first query lasts
59403ms, the second query 31 ms ( the desc order variant has the same large difference: 122494 ms vs. 1297 ms). (for
thequery plans see below). 

The reason for the difference is that the mergejoin plan has a much
lower startup cost than the hash plan, and since you're only fetching
100 rows the startup cost is dominant.  IIRC the planner does make some
allowance for this effect when preparing a DECLARE CURSOR plan (ie,
it puts some weight on startup cost rather than considering only total
cost) ... but it's not so optimistic as to assume that you only want 100
out of an estimated 1 million+ result rows.

The best solution is probably to put a LIMIT into the DECLARE CURSOR,
so that the planner can see how much you intend to fetch.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Joost Kraaijeveld"
Date:
Subject: Retry: Is this possible / slow performance?
Next
From: Gaetano Mendola
Date:
Subject: Re: Are JOINs allowed with DELETE FROM