explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vK1E

Settings
# exclusive inclusive rows x rows loops node
1. 501.816 517.709 ↓ 2.2 1,070 1

GroupAggregate (cost=7,414.15..8,620.35 rows=478 width=40) (actual time=15.846..517.709 rows=1,070 loops=1)

  • Group Key: sa.id
2. 0.927 15.893 ↓ 2.8 1,497 1

Sort (cost=7,414.15..7,415.49 rows=537 width=456) (actual time=15.683..15.893 rows=1,497 loops=1)

  • Sort Key: sa.id
  • Sort Method: quicksort Memory: 1066kB
3. 1.184 14.966 ↓ 2.8 1,497 1

Hash Join (cost=6,653.66..7,389.80 rows=537 width=456) (actual time=13.169..14.966 rows=1,497 loops=1)

  • Hash Cond: (sageom.entityid = sa.id)
4. 0.650 0.650 ↑ 1.0 9,023 1

Seq Scan on geometryspecialarea sageom (cost=0.00..622.23 rows=9,023 width=456) (actual time=0.029..0.650 rows=9,023 loops=1)

5. 0.155 13.132 ↓ 2.2 1,070 1

Hash (cost=6,647.69..6,647.69 rows=478 width=4) (actual time=13.132..13.132 rows=1,070 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 54kB
6. 0.162 12.977 ↓ 2.2 1,070 1

Unique (cost=6,121.70..6,642.91 rows=478 width=4) (actual time=10.293..12.977 rows=1,070 loops=1)

7. 0.000 12.815 ↓ 2.8 1,497 1

Nested Loop (cost=6,121.70..6,641.56 rows=537 width=4) (actual time=10.292..12.815 rows=1,497 loops=1)

8. 0.566 11.496 ↓ 2.8 1,497 1

Merge Join (cost=6,121.42..6,395.08 rows=537 width=8) (actual time=10.281..11.496 rows=1,497 loops=1)

  • Merge Cond: (sa.id = gis.entityid)
9. 0.303 10.323 ↓ 2.2 1,070 1

Sort (cost=6,121.14..6,122.33 rows=478 width=8) (actual time=10.264..10.323 rows=1,070 loops=1)

  • Sort Key: sa.id
  • Sort Method: quicksort Memory: 99kB
10. 10.020 10.020 ↓ 2.2 1,070 1

Seq Scan on specialarea sa (cost=0.00..6,099.86 rows=478 width=8) (actual time=0.031..10.020 rows=1,070 loops=1)

  • Filter: ((NOT isdeleted) AND isactive AND ((todate >= to_timestamp('20190208'::text, 'yyyyMMdd'::text)) OR (todate IS NULL)) AND (fromdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND ((todate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) OR (todate IS NULL)) AND (organisationid = ANY ('{4382,1070,4683,4398,4862,4684,4685,4686,4710,4399,1088,4512,4809,4529,4401,1072,4680,4530,4810,4513,4661,1338,4479,4811,4691,4688,4812,4597,1307,4856,4838,5463,4813,4663,1308,4406,4514,4408,4665,1095,1330,4667,4532,4712,4386,4692,1344,1312,4641,4387,4818,4819,4820,4672,4821,1315,4391,1098,1076,1077,1096,4703,1103,4644,4393,4678,4693,4679,4694,4518,4599,4704,1346,1347,4675,4674,4668,4816,4394,4824,4594,4825,4817,4851,4706,4721,4396,1318,4600,4714,4670,4671,4423,1104,4822,4724,4586,4649,4697,4884,4728,4464,4806,1322,4677,1334,4801,4424,4814,4603,1080,1106,4640,4428,4650,4465,1107,4808,4429,5505,4807,4695,4430,1108,4520,1335,5026,4771,4772,4773,4774,4775,4776,4754,4755,4756,4757,4758,4759,4760,4761,4762,4763,4764,4765,5751,4787,4766,4803,4767,4768,4770,4777,4860,4778,4780,4782,4783,4784,4785,4786,4769,1945,1944,1943,1939,1465,1462,1464,1463,4939,1001,2230,1707,2345,5968,4583,4584,4585,5969,1101,1105,1724,3770,4390,2286,2287,2288,2221,1282,2331,915,2127,2128,734,736,735,1722,229,238,252,254,5944,230,231,237,251,249,250,232,239,1655,1656,235,243,1793,244,236,248,247,246,245,234,1034,1037,1035,1036,1033,233,240,1792,2363,1445,1482,2953,2512,1607,4711,4699,3316,1073,1645,2736,2732,2737,2733,2738,2739,2740,2741,1310,1314,1319,1090,1074,2210,5258,1354,5269,309,4655,3235,4405,2213,1473,737,444,2124,2066,2132,2139,2138,3730,3731,3732,3733,3143,1093,1109,3063,1094,50,2305,2304,2307,2308,2306,4436,859,4666,1356,4357,2233,4669,2982,1867,2238,1075,3111,1483,3364,2212,2473,1706,4713,4750,3551,1506,1468,2234,1215,1217,1218,1216,1097,766,2039,4579,3517,1700,1840,5018,3519,3502,575,4534,914,942,941,943,1474,1914,1453,1454,1455,1456,2291,1078,1457,1461,4527,1317,1708,1358,1687,4705,747,2175,2176,326,1359,1460,1702,1703,4449,1815,1816,1817,1818,1701,999,4673,3148,3147,701,707,706,708,61,1186,1187,4727,4564,4740,3500,3585,3586,3587,1364,4439,2909,2939,2118,1133,3845,1394,5469,1309,1302,1316,1693,1452,4426,1458,3520,1323,1031,4932,2089,1699,76,2069,3693,1471,3146,4737,3539,5645,2135,2136,2137,2144,2133,2145,1704,1775,1720,1721,457,152,330,331,332,146,99,151,2957,324,325,1755,4659,1746,1747,1748,1752,1749,1750,1751,2080,2082,2938,2475,2086,4642,4861,2083,2081,2084,2085,1666,1340,1341,1342,1343,1345,1360,1351,2046,1339,1350,3132,3134,913,3133,3472,5757,4397,4400,4407,3427,4524,4707,3315,4708,3314,3266,3313,4637,4676,3841,4636,4402,1839,4656,3406,4598,4664,1841,1838,4546,4403,5066,4630,4515,4646,4731,4435,1964,4858,4422,1837,5133,3154,3426,4459,3264,4651,4658,4662,4657,3758,3886,4645,4395,4487,3267,2731,3408,4732,3451,5810,1661,1662,1226,631,461,612,459,1332,1227,458,4823,460,630,5497,1663,1660,5462,4902,1798,2548,5847,2020,4383,3409,3656,3663,3662,3657,3661,3665,3664,3660,3666,3659,3658,3669,3668,3667}'::integer[])))
  • Rows Removed by Filter: 6960
11. 0.607 0.607 ↑ 1.0 9,023 1

Index Only Scan using ix_geometryspecialareaid on geometryspecialarea gis (cost=0.29..243.63 rows=9,023 width=4) (actual time=0.012..0.607 rows=9,023 loops=1)

  • Heap Fetches: 0
12. 1.497 1.497 ↑ 1.0 1 1,497

Index Only Scan using pk_organisation on organisation o (cost=0.28..0.46 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,497)

  • Index Cond: (id = sa.organisationid)
  • Heap Fetches: 0