Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 81DF29F9D05 for ; Fri, 21 Sep 2007 17:05:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 48987-06 for ; Fri, 21 Sep 2007 17:05:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fmx07.freemail.hu (fmx07.freemail.hu [195.228.245.57]) by postgresql.org (Postfix) with SMTP id 47DBF9F99EC for ; Fri, 21 Sep 2007 17:05:30 -0300 (ADT) Received: (qmail 53581 invoked from network); 21 Sep 2007 22:05:29 +0200 Received: from fm03.freemail.hu (195.228.245.103) by fmx07.freemail.hu with SMTP; 21 Sep 2007 22:05:29 +0200 Received: (qmail 11371 invoked by uid 1909201); 21 Sep 2007 22:05:29 +0200 Date: Fri, 21 Sep 2007 22:05:29 +0200 (CEST) From: Denes Daniel Subject: Re: Query planner unaware of possibly best plan To: Alvaro Herrera cc: pgsql-performance@postgresql.org In-Reply-To: <20070921195644.GB13986@alvh.no-ip.org> Message-ID: X-Originating-IP: [193.226.224.189] X-HTTP-User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727) MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="0-1804289383-1190405129=:11365" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/387 X-Sequence-Number: 26932 --0-1804289383-1190405129=:11365 Content-Type: TEXT/PLAIN; CHARSET=ISO-8859-2 In reply to Alvaro Herrera: > The best thing to do is paste them in a text file and send it as an > attachment. Okay, it's attached. > Why did you set enable_sort=off? It's not like sorting 9 rows is going > to take any noticeable amount of time anyway. Of course it's no problem for 9 rows, but this is only a test case. In production there will be much more. I just wanted to show that the planner doesn't even consider a plan without a sort step, using purely index scans. Denes Daniel ------------------------------------------------------------ Olvasd az [origo]-t a mobilodon: mini magazinok a Mobizin-en ___________________________________________________ www.t-mobile.hu/mobizin --0-1804289383-1190405129=:11365 Content-Type: TEXT/plain; name="expana.txt"; CHARSET=ISO-8859-2 Content-Disposition: attachment; filename="expana.txt" EXPLAIN ANALYZE SELECT * FROM tparent JOIN tchild ON tchild.par_id = tparent.id WHERE tparent.ord BETWEEN 1 AND 4 ORDER BY tparent.ord, tchild.ord; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=100000132.10..100000140.10 rows=8 width=16) (actual time=0.302..0.319 rows=9 loops=1) Sort Key: tparent.ord, tchild.ord -> Nested Loop (cost=0.00..84.10 rows=8 width=16) (actual time=0.181..0.267 rows=9 loops=1) -> Index Scan using par_uniq_ord on tparent (cost=0.00..20.40 rows=4 width=8) (actual time=0.100..0.109 rows=4 loops=1) Index Cond: ((ord >= 1) AND (ord <= 4)) -> Index Scan using chi_pkey_parid_ord on tchild (cost=0.00..9.93 rows=2 width=8) (actual time=0.020..0.026 rows=2 loops=4) Index Cond: (tchild.par_id = "outer".id) Total runtime: 0.412 ms (8 rows) --0-1804289383-1190405129=:11365--