pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PFC <lists@boutiquenumerique.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>
Cc: Pgsql-Performance (E-mail) <pgsql-performance@postgresql.org>
Subject: Re: Retry: Is this possible / slow performance?
Date: Mon, 07 Feb 2005 19:16:47 +0100
Message-ID: <opsluad9bhth1vuj@musicbox> (raw)
In-Reply-To: <27755.1107795836@sss.pgh.pa.us>
References: <A3D1526C98B7C1409A687E0943EAC410605EF2@obelix.askesis.nl>
	<27755.1107795836@sss.pgh.pa.us>


	Does the planner also take into account that the Hash Join will need a  
huge temporary space which will exist for the whole length of the cursor  
existence (which may be quite long if he intends to fetch everything),  
whereas the Merge Join should need very little space as it is sending the  
rows as it fetches them using the Indexes ?




On Mon, 07 Feb 2005 12:03:56 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "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 59403 ms, the second query  
>> 31 ms ( the desc order variant has the same large difference: 122494 ms  
>> vs. 1297 ms). (for the query 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>





view thread (5+ messages)  latest in thread

Message-ID: <opsluad9bhth1vuj@musicbox>
Permalink:  ../opsluad9bhth1vuj@musicbox/
Also on:    postgresql.org/message-id/opsluad9bhth1vuj@musicbox

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: lists@boutiquenumerique.com, tgl@sss.pgh.pa.us, J.Kraaijeveld@askesis.nl
  Subject: Re: Retry: Is this possible / slow performance?
  In-Reply-To: <opsluad9bhth1vuj@musicbox>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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