add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
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
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