X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 803BED1B478 for ; Fri, 9 Jan 2004 16:42:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30156-10 for ; Fri, 9 Jan 2004 12:42:08 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0625BD1DC76 for ; Fri, 9 Jan 2004 11:07:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i09F7919023793; Fri, 9 Jan 2004 10:07:09 -0500 (EST) To: Richard Huxton Cc: Dennis =?iso-8859-1?q?Bj=F6rklund?= , Bradley Tate , pgsql-performance@postgresql.org Subject: Re: Slow query problem In-reply-to: <200401090919.04718.dev@archonet.com> References: <200401090919.04718.dev@archonet.com> Comments: In-reply-to Richard Huxton message dated "Fri, 09 Jan 2004 09:19:04 +0000" Date: Fri, 09 Jan 2004 10:07:09 -0500 Message-ID: <23792.1073660829@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/100 X-Sequence-Number: 5275 Richard Huxton writes: >> The goal was to avoid the sorting which should not be needed with that >> index (I hope). So I still think that it would help in this case. > Sorry - not being clear. I can see how it _might_ help, but will the planner > take into account the fact that even though: > index-cost > seqscan-cost > that > (index-cost + no-sorting) < (seqscan-cost + sort-cost) Yes, it would. > assuming of course, that the costs turn out that way. That I'm less sure about. A sort frequently looks cheaper than a full indexscan, unless the table is pretty well clustered on that index, or you knock random_page_cost way down. With no stats at all, CVS tip has these preferences: regression=# create table fooey (f1 int, f2 int, unique(f1,f2)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "fooey_f1_key" for table "fooey" CREATE TABLE regression=# explain select * from fooey group by f1,f2; QUERY PLAN --------------------------------------------------------------- HashAggregate (cost=25.00..25.00 rows=1000 width=8) -> Seq Scan on fooey (cost=0.00..20.00 rows=1000 width=8) (2 rows) regression=# set enable_hashagg TO 0; SET regression=# explain select * from fooey group by f1,f2; QUERY PLAN ------------------------------------------------------------------------------------ Group (cost=0.00..57.00 rows=1000 width=8) -> Index Scan using fooey_f1_key on fooey (cost=0.00..52.00 rows=1000 width=8) (2 rows) regression=# set enable_indexscan TO 0; SET regression=# explain select * from fooey group by f1,f2; QUERY PLAN --------------------------------------------------------------------- Group (cost=69.83..77.33 rows=1000 width=8) -> Sort (cost=69.83..72.33 rows=1000 width=8) Sort Key: f1, f2 -> Seq Scan on fooey (cost=0.00..20.00 rows=1000 width=8) (4 rows) but remember this is for a relatively small (estimated size of) table. regards, tom lane