add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
donate feedback about
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
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 =
                    -> 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 =
              -> 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 =
        -> 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 =
              Filter: (id > 30000)
              Rows Removed by Filter: 3
Planning Time: 0.806 ms
Execution Time: 3.994 ms
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