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 35B2AD1B532
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Oct 2003 13:26:24 +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 12457-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Oct 2003 10:25:55 -0300 (ADT)
Received: from web13807.mail.yahoo.com (web13807.mail.yahoo.com
	[216.136.175.17])
	by svr1.postgresql.org (Postfix) with SMTP id A0FFCD1B535
	for <pgsql-sql@postgresql.org>; Wed, 22 Oct 2003 10:25:51 -0300 (ADT)
Message-ID: <20031022132551.14517.qmail@web13807.mail.yahoo.com>
Received: from [216.173.173.66] by web13807.mail.yahoo.com via HTTP;
	Wed, 22 Oct 2003 06:25:51 PDT
Date: Wed, 22 Oct 2003 06:25:51 -0700 (PDT)
From: Chris Gamache <cgg007@yahoo.com>
Reply-To: cgg007@yahoo.com
Subject: Query planner: current_* vs. explicit date
To: pgsql-sql <pgsql-sql@postgresql.org>
In-Reply-To: <1066826186.3004.10.camel@jester>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0
	tests=FROM_ENDS_IN_NUMS, IN_REP_TO
X-Spam-Level: 
X-Archive-Number: 200310/287
X-Sequence-Number: 15687

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