Explain plan for 2 column index
От | |
---|---|
Тема | Explain plan for 2 column index |
Дата | |
Msg-id | 0A5B2E3C3A64CA4AB14F76DBCA76DDA44EF9B4@seifur.hnit.is обсуждение исходный текст |
Ответы |
Re: Explain plan for 2 column index
|
Список | 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
В списке pgsql-performance по дате отправления: