X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 21C01D1B551 for ; Wed, 22 Oct 2003 19:24:46 +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 70259-10 for ; Wed, 22 Oct 2003 16:24:15 -0300 (ADT) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 6F6D1D1B557 for ; Wed, 22 Oct 2003 16:24:08 -0300 (ADT) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id h9MJNEDd003712; Wed, 22 Oct 2003 13:23:14 -0600 (MDT) Date: Wed, 22 Oct 2003 13:12:01 -0600 (MDT) From: "scott.marlowe" To: Chris Gamache Cc: pgsql-sql Subject: Re: Query planner: current_* vs. explicit date In-Reply-To: <20031022132551.14517.qmail@web13807.mail.yahoo.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/289 X-Sequence-Number: 15689 I'd guess that the planner doesn't know what current_date::timestamp is ahead of time, so it chooses a seq scan. On Wed, 22 Oct 2003, Chris Gamache wrote: > PsotgreSQL 7.2.4: > > Query planner is behaving strangely. It operates differently for explicit dates > and derived dates... any ideas on why? ( and why I might not have noticed this > before... ) > > CREATE TABLE trans_table ( > id serial, > user_name varchar(50), > trans_type varchar(50), > trans_data varchar(50), > trans_date timestamptz, > trans_uuid uniqueidentifier, > CONSTRAINT trans_table_pkey PRIMARY KEY (id) > ) WITH OIDS; > > ... Insert lots of data ... > > CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date); > CREATE INDEX trans_table_user_date_idx ON trans_table USING btree > (user_name,trans_date); > CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid); > > VACUUM ANALYZE trans_table; > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp; > Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4) > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00 > AM'::timestamp; > Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69 > rows=417 width=4) > > CG > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >