From: | Chris Gamache <cgg007(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Query planner: current_* vs. explicit date |
Date: | 2003-10-22 13:25:51 |
Message-ID: | 20031022132551.14517.qmail@web13807.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesmann | 2003-10-22 17:07:53 | Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
Previous Message | Rod Taylor | 2003-10-22 12:36:27 | Re: see a current query |