I'd guess that the planner doesn't know what current_date::timestamp is ahead of time, so it chooses a seq scan.
On Wed, 22 Oct 2003, Chris Gamache wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
