From: | <lnd(at)hnit(dot)is> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Explain plan for 2 column index |
Date: | 2004-01-29 19:29:59 |
Message-ID: | 0A5B2E3C3A64CA4AB14F76DBCA76DDA44EF9B4@seifur.hnit.is |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-01-29 19:31:11 | Re: query optimization question |
Previous Message | Josh Berkus | 2004-01-29 19:21:54 | Re: [PERFORM] Set-Returning Functions WAS: On the performance of views |