| 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: | Whole Thread | Raw Message | 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 |