By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
10000 rows affected
100000 rows affected
100000 rows affected
100000 rows affected
200000 rows affected
10000 rows affected
count |
---|
10000 |
count |
---|
9346 |
QUERY PLAN |
---|
Limit (cost=1.58..3582.40 rows=400 width=232) (actual time=0.901..3.915 rows=400 loops=1) |
-> Nested Loop (cost=1.58..6631833.85 rows=740817 width=232) (actual time=0.900..3.871 rows=400 loops=1) |
-> Nested Loop Left Join (cost=1.16..3711337.27 rows=112200 width=156) (actual time=0.622..2.926 rows=20 loops=1) |
-> Nested Loop Left Join (cost=0.87..1942561.27 rows=112200 width=116) (actual time=0.619..2.000 rows=14 loops=1) |
-> Nested Loop Left Join (cost=0.58..173669.27 rows=112200 width=76) (actual time=0.445..0.607 rows=11 loops=1) |
-> Index Scan using first_title on first (cost=0.29..872.30 rows=10668 width=36) (actual time=0.231..0.241 rows=2 loops=1) |
-> Index Scan using second_first_id on second (cost=0.29..10.59 rows=561 width=40) (actual time=0.142..0.177 rows=6 loops=2) |
Index Cond: (first_id = first.id) |
-> Index Scan using third_second_id on third (cost=0.29..10.16 rows=561 width=40) (actual time=0.123..0.124 rows=1 loops=11) |
Index Cond: (second_id = second.id) |
-> Index Scan using fourth_third_id on fourth (cost=0.29..10.15 rows=561 width=40) (actual time=0.061..0.064 rows=1 loops=14) |
Index Cond: (third_id = third.id) |
-> Index Scan using fifth_first_id on fifth (cost=0.42..22.51 rows=352 width=44) (actual time=0.020..0.042 rows=20 loops=20) |
Index Cond: (first_id = first.id) |
Filter: (id > 30000) |
Rows Removed by Filter: 3 |
Planning Time: 0.806 ms |
Execution Time: 3.994 ms |
QUERY PLAN |
---|
Limit (cost=2112.56..2112.82 rows=104 width=68) (actual time=296.469..296.536 rows=400 loops=1) |
-> Sort (cost=2112.56..2112.82 rows=104 width=68) (actual time=296.467..296.505 rows=400 loops=1) |
Sort Key: ((data -> 'title'::text)) |
Sort Method: top-N heapsort Memory: 601kB |
-> Seq Scan on all_json (cost=0.00..2109.07 rows=104 width=68) (actual time=0.039..276.074 rows=9346 loops=1) |
Filter: (data @? '$."second"[*]."third"[*]."fourth"[*]."fifth"[*]."id"?(@ > 30000)'::jsonpath) |
Rows Removed by Filter: 654 |
Planning Time: 0.101 ms |
Execution Time: 296.587 ms |