explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Sdl

Settings
# exclusive inclusive rows x rows loops node
1. 3.629 11,474.551 ↓ 310.5 6,831 1

Nested Loop (cost=18,930.10..113,077.25 rows=22 width=1,180) (actual time=2,308.133..11,474.551 rows=6,831 loops=1)

2. 4.522 11,443.598 ↓ 310.5 6,831 1

Merge Join (cost=18,929.66..113,001.87 rows=22 width=1,143) (actual time=2,308.121..11,443.598 rows=6,831 loops=1)

  • Merge Cond: (contract_template.permittype_id = permittype.permittype_id)
3. 6,903.303 11,413.024 ↓ 11.9 6,832 1

Nested Loop (cost=18,929.38..267,583.04 rows=575 width=123) (actual time=2,305.996..11,413.024 rows=6,832 loops=1)

  • Join Filter: (contract.contract_template_id = contract_template.contract_template_id)
  • Rows Removed by Join Filter: 50795920
4. 10.941 10.941 ↑ 1.0 7,436 1

Index Scan using contract_template_permittype_id on contract_template (cost=0.28..3,722.18 rows=7,436 width=16) (actual time=0.006..10.941 rows=7,436 loops=1)

5. 3,128.694 4,498.780 ↓ 11.9 6,832 7,436

Materialize (cost=18,929.10..199,726.80 rows=575 width=107) (actual time=0.177..0.605 rows=6,832 loops=7,436)

6. 8.847 1,370.086 ↓ 11.9 6,832 1

Gather (cost=18,929.10..199,723.92 rows=575 width=107) (actual time=1,315.301..1,370.086 rows=6,832 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 808.665 1,361.239 ↓ 9.5 2,277 3

Parallel Bitmap Heap Scan on contract (cost=17,929.10..198,666.42 rows=240 width=107) (actual time=1,317.224..1,361.239 rows=2,277 loops=3)

  • Recheck Cond: (operator_id = 21)
  • Filter: ((NOT archived) AND (valid_from <> valid_to) AND (created_at >= '2019-04-30 22:00:00+00'::timestamp with time zone) AND (created_at <= '2019-05-31 21:59:59+00'::timestamp with time zone) AND (contract_type = 'permit'::contract_type))
  • Rows Removed by Filter: 246465
  • Heap Blocks: exact=27758
8. 552.574 552.574 ↓ 1.1 852,701 1

Bitmap Index Scan on contract_operator_id_valid_from_valid_to_archived_idx (cost=0.00..17,928.96 rows=787,341 width=0) (actual time=552.574..552.574 rows=852,701 loops=1)

  • Index Cond: ((operator_id = 21) AND (archived = false))
9. 26.052 26.052 ↑ 1.0 109 1

Index Scan using permittype_pkey on permittype (cost=0.28..231.86 rows=109 width=1,020) (actual time=1.736..26.052 rows=109 loops=1)

  • Index Cond: (permittype_id = ANY ('{803,1318,1212,1211,1218,1224,1227,1321,1214,1219,2804,2803,771,792,779,1208,1239,1251,1248,1222,786,1237,1238,1240,1247,1242,1313,1217,1319,1320,787,788,797,794,1314,1317,1190,1215,1316,3493,791,3492,3494,1225,1246,1223,1221,1235,1236,1243,1244,1245,1315,1209,1220,1234,1250,1229,1230,1231,1216,1232,1233,1138,1137,1139,1047,1140,1141,1134,780,795,796,800,801,781,1147,1133,802,1136,1187,3072,784,1322,773,1142,774,1210,1144,1184,1186,1132,1143,1146,1189,782,1228,783,793,1188,785,775,1145,1185,798,1249,1226,1213,1241}'::integer[]))
10. 27.324 27.324 ↑ 1.0 1 6,831

Index Scan using contract_purchase_pkey on contract_purchase (cost=0.43..3.43 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=6,831)

  • Index Cond: (contract_purchase_id = contract.contract_purchase_id)