Re: Execution plan does not use index - Mailing list pgsql-general
| From | Michael Lewis |
|---|---|
| Subject | Re: Execution plan does not use index |
| Date | |
| Msg-id | CAHOFxGqJE1BsTOqS2sbs6k7Lw--atw0Xufio263xHWzfa_tT=g@mail.gmail.com Whole thread Raw |
| In response to | Re: Execution plan does not use index (Peter Coppens <peter.coppens@datylon.com>) |
| Responses |
Re: Execution plan does not use index
|
| List | pgsql-general |
On Tue, Nov 10, 2020 at 1:25 AM Peter Coppens <peter.coppens@datylon.com> wrote:
Triggered by Michael mentioning subqueries I ended up tryingexplainselect d.short_id,mv.timestamp ,mv.I64_01from device d, device_configuration dc, measurement_value mvwhere mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and==>Nested Loop (cost=25.85..84540074.64 rows=16996885 width=20)-> Seq Scan on device_configuration dc (cost=0.00..470.01 rows=615 width=30)Filter: latest-> Nested Loop (cost=25.85..137027.83 rows=43494 width=36)-> Index Scan using device_pkey on device d (cost=0.28..7.23 rows=1 width=20)Index Cond: (id = dc.device_id)-> Index Scan using measurement_values_pkey on measurement_value mv (cost=25.58..136585.66 rows=43494 width=20)Index Cond: ((device_id = d.short_id) AND ("timestamp" > ('2020-11-06 00:00:00'::timestamp without time zone - (SubPlan 1))) AND ("timestamp" < ('2020-11-07 00:00:00'::timestamp without time zone - (SubPlan 2))))SubPlan 1-> Function Scan on pg_timezone_names (cost=0.00..12.50 rows=5 width=16)Filter: (name = dc.timezone)SubPlan 2-> Function Scan on pg_timezone_names pg_timezone_names_1 (cost=0.00..12.50 rows=5 width=16)Filter: (name = dc.timezone)Now returns the 320K in less than 5sec.I was till now convinced that correlated subqueries or joins are equivalent. I guess I was wrong :). Wonder how stable this plan will be though
Curious, what is seq_page_cost and random_page_cost? Any idea of your cache hits for indexes? If they are very high and/or you have SSD or similar fast storage, then maybe random_page_cost should be 1.1-2 and not default 4 (assuming seq_page_cost is still 1). The planner will be more likely to use an index scan if the expected cost for scanning an index (random) is closer to a sequential read. Sorry if this explanation is completely superfluous and you have already configured this.
It would be interesting to see explain (analyze buffers) output so we can see the actual counts for these nodes. I'm rather surprised that the query I provided didn't use the timestamp index unless the inclusion of 3 days worth of range meant that it was estimated to be too high a fraction of the table. If you just execute only the subquery, is the index used by chance?
Top posting (reply with all previous copied below) is discouraged on these lists. I think because it makes the archives rather messy.
pgsql-general by date: