Thread: Explain plan for 2 column index
I have 2 columns index. The question is if optimizer can use both columns of an index or not, i.e. the plan should read like this: Index Cond: ((name)::text = 'name1'::text) AND ((date_from)::timestamp with time zone= ('now'::text)::timestamp(6) with time zone) Whilst I am getting index scan on first column and filter on the other: Index Scan using testtab_name_date_from on testtab (cost=0.00..2.01 rows=1 width=18) Index Cond: ((name)::text = 'name1'::text) Filter: ((date_from)::timestamp with time zone = ('now'::text)::timestamp(6)with time zone) Could the problem be timestamp column or timestamp with time zones? Thank you, Laimis ------------------------------------------- Bellow are details of the test: Create table testtab (name varchar(10), date_from timestamp); create index testtab_name_date_from on testtab(name, date_from) ; populated table with pseudo random data (10000), analyzed and tuned optimizer to favour indexes instead of sequential scans. Pg config: random_page_cost = 0 cpu_index_tuple_cost = 0.0 enable_seqscan = false cpu_tuple_cost = 1
On Thursday 29 January 2004 19:29, lnd@hnit.is wrote: > I have 2 columns index. > The question is if optimizer can use both columns of an index or not, Should do. > i.e. the plan should read like this: > > Index Cond: > ((name)::text = 'name1'::text) > AND ((date_from)::timestamp with time zone= > ('now'::text)::timestamp(6) with time zone) > > Whilst I am getting index scan on first column and filter on the other: > > Index Scan using testtab_name_date_from on testtab (cost=0.00..2.01 > rows=1 width=18) > Index Cond: ((name)::text = 'name1'::text) > Filter: ((date_from)::timestamp with time zone = > ('now'::text)::timestamp(6)with time zone) > > Could the problem be timestamp column or timestamp with time zones? What types are the columns here? If date_from isn't timestamp with time zone, that might be the issue. Also, I'm not convinced timestamp is the same thing as timestamp(6) - why the different accuracies. Also, note that 'now' is deprecated - now() or CURRENT_TIMESTAMP/DATE/etc are preferred. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: >> Index Scan using testtab_name_date_from on testtab (cost=0.00..2.01 >> rows=1 width=18) >> Index Cond: ((name)::text = 'name1'::text) >> Filter: ((date_from)::timestamp with time zone = >> ('now'::text)::timestamp(6)with time zone) > What types are the columns here? If date_from isn't timestamp with time zone, > that might be the issue. It clearly isn't, since we can see a coercion to timestamp with time zone in the query. My guess is that the original SQL was WHERE ... date_from = current_timestamp This should be WHERE ... date_from = localtimestamp if timestamp without tz is the intended column datatype. Of course, it might just be that date_from was declared as the wrong type (it really sucks that SQL specifies "timestamp" to default to "without time zone" ...) regards, tom lane