From: Chris Gamache <cgg007@yahoo.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Query planner: current_* vs. explicit date
Date: Wed, 22 Oct 2003 06:25:51 -0700 (PDT)
Message-ID: <20031022132551.14517.qmail@web13807.mail.yahoo.com> (raw)
In-Reply-To: <1066826186.3004.10.camel@jester>
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
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-sql@postgresql.org
Cc: cgg007@yahoo.com
Subject: Re: Query planner: current_* vs. explicit date
In-Reply-To: <20031022132551.14517.qmail@web13807.mail.yahoo.com>
* 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