Query planner: current_* vs. explicit date

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

In response to

Responses

Browse pgsql-sql by date

  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