explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JRjF : Limit (cost=158452.14..158452.15 rows=4 width=1233) (actual time=89638.020..89638.066 rows=20 loops=1) -> Sort (cost=158452.14..158452.15 rows=4 width=1233) (actual time=89638.016..89638.025 rows=20 loops=1) Sort Key: n.id DESC Sort Method: top-N heapsort Memory: 61kB -> WindowAgg (cost=115248.21..158452.06 rows=4 width=1233) (actual time=89637.873..89637.935 rows=57 loops=1) -> Nested Loop Left Join (cost=115248.21..158452.01 rows=4 width=1225) (actual time=1562.003..89637.412 rows=57 loops=1) -> Nested Loop Left Join (cost=115247.79..158418.25 rows=4 width=1224) (actual time=1561.987..89636.211 rows=57 loops=1) -> Hash Right Join (cost=14889.96..15017.57 rows=4 width=1212) (actual time=15.555..15.765 rows=57 loops=1) Hash Cond: (nh2.notice_id = n.id) -> HashAggregate (cost=14827.11..14883.51 rows=5640 width=16) (actual time=10.151..12.024 rows=2193 loops=1) Group Key: nh2.notice_id -> Bitmap Heap Scan on notice_history nh2 (cost=112.34..14798.78 rows=5666 width=16) (actual time=0.825..6.259 rows=2314 loops=1) Recheck Cond: ("r$notice_status_id" = 11) Heap Blocks: exact=1174 -> Bitmap Index Scan on idx_notice_history_notice_status_id (cost=0.00..110.92 rows=5666 width=0) (actual time=0.411..0.411 rows=2314 loops=1) Index Cond: ("r$notice_status_id" = 11) -> Hash (cost=62.81..62.81 rows=4 width=1204) (actual time=1.872..1.873 rows=57 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 44kB -> Bitmap Heap Scan on notice n (cost=46.78..62.81 rows=4 width=1204) (actual time=1.207..1.699 rows=57 loops=1) Recheck Cond: ((date_in >= '2019-05-01 00:00:00'::timestamp without time zone) AND (date_in <= '2019-05-31 00:00:00'::timestamp without time zone) AND ((region)::text = '52'::text)) Filter: ("r$notice_status_id" <> ALL ('{1,8,11}'::integer[])) Rows Removed by Filter: 126 Heap Blocks: exact=84 -> BitmapAnd (cost=46.78..46.78 rows=4 width=0) (actual time=1.156..1.156 rows=0 loops=1) -> Bitmap Index Scan on idx_notice__date_in (cost=0.00..21.33 rows=891 width=0) (actual time=0.060..0.061 rows=208 loops=1) Index Cond: ((date_in >= '2019-05-01 00:00:00'::timestamp without time zone) AND (date_in <= '2019-05-31 00:00:00'::timestamp without time zone)) -> Bitmap Index Scan on idx_notice_region (cost=0.00..25.19 rows=2236 width=0) (actual time=1.077..1.078 rows=2610 loops=1) Index Cond: ((region)::text = '52'::text) -> Hash Join (cost=100357.82..111118.53 rows=1 width=20) (actual time=1569.507..1569.785 rows=1 loops=57) Hash Cond: ((min(nh_1.id)) = nh.id) -> GroupAggregate (cost=100341.42..107074.06 rows=319054 width=16) (actual time=660.173..1360.425 rows=467368 loops=57) Group Key: nh_1.notice_id -> Sort (cost=100341.42..101522.12 rows=472281 width=16) (actual time=660.165..893.514 rows=468879 loops=57) Sort Key: nh_1.notice_id Sort Method: external merge Disk: 11944kB -> Bitmap Heap Scan on notice_history nh_1 (cost=9116.61..47756.12 rows=472281 width=16) (actual time=14.822..293.252 rows=468879 loops=57) Recheck Cond: ("r$notice_status_id" = 2) Heap Blocks: exact=933432 -> Bitmap Index Scan on idx_notice_history_notice_status_id (cost=0.00..8998.53 rows=472281 width=0) (actual time=12.297..12.297 rows=468879 loops=57) Index Cond: ("r$notice_status_id" = 2) -> Hash (cost=16.37..16.37 rows=3 width=28) (actual time=0.030..0.030 rows=3 loops=57) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Index Scan using idx_notice_history_notice_id on notice_history nh (cost=0.43..16.37 rows=3 width=28) (actual time=0.018..0.022 rows=3 loops=57) Index Cond: (n.id = notice_id) -> Index Scan using missing_address_pkey on missing_address ma (cost=0.42..8.44 rows=1 width=9) (actual time=0.011..0.011 rows=0 loops=57) Index Cond: (id = n.missing_address_id) Planning time: 32.518 ms Execution time: 89763.396 ms

Settings
# exclusive inclusive rows x rows loops node
1. 0.158 55.012 ↑ 11.4 71 1

Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)

  • Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
  • Sort Method: quicksort Memory: 43kB
2. 1.378 54.854 ↑ 11.4 71 1

Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)

  • Hash Cond: (p.pronamespace = n.oid)
3. 53.465 53.465 ↓ 3.0 2,402 1

Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2,402 loops=1)

  • Filter: pg_function_is_visible(oid)
4. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
5. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))