Tom Lane wrote: > which is almost 80% of the entire runtime. Which is enormous. > What are those column datatypes exactly?
Table "richard.sessions" Column | Type | Modifiers ------------------------+-----------------------------+----------- [unrelated columns removed] starttimetrunc | timestamp without time zone | finishtimetrunc | timestamp without time zone | Indexes: "rb_us_st_ft_idx" btree (starttimetrunc, finishtimetrunc) "rb_us_st_ft_idx2" btree (finishtimetrunc, starttimetrunc) Check constraints: "date_check" CHECK (finishtimetrunc >= starttimetrunc) Table "richard.duration" Column | Type | Modifiers --------+-----------------------------+----------- ts | timestamp without time zone | > Perhaps you are incurring a datatype conversion cost? Not that I can tell. > It seems more likely that the cpu_operator_cost is underestimated, As you perdicted, increasing cpu_operator_cost from 0.0025 to 0.025 also causes the planner to use the index on duration. > which leads me to question what exactly is happening in those > comparisons. Your guess is as good as mine (actually, yours is much better). I can put together a reproducable test case if you like.. -- Richard van den Berg, CISSP ------------------------------------------- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a | www.trust-factory.com 2518AK The Hague | Phone: +31 70 3620684 The Netherlands | Fax : +31 70 3603009 ------------------------------------------- Have you visited our new DNA Portal? ------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match