explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uaxT

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 1,523.747 ↓ 1.1 211 1

Unique (cost=50,358.32..50,436.05 rows=200 width=5) (actual time=1,523.690..1,523.747 rows=211 loops=1)

2.          

CTE cte

3. 17.310 1,523.530 ↑ 37.2 418 1

HashAggregate (cost=48,809.50..48,964.97 rows=15,547 width=5) (actual time=1,523.389..1,523.530 rows=418 loops=1)

  • Group Key: departures.departure_date, departures.direct_flight
4. 8.565 1,506.220 ↓ 7.6 118,757 1

Append (cost=15,702.38..48,731.76 rows=15,547 width=5) (actual time=1,459.596..1,506.220 rows=118,757 loops=1)

5. 1,390.101 1,443.029 ↓ 0.0 0 1

Bitmap Heap Scan on departures (cost=15,702.38..20,653.00 rows=2,426 width=5) (actual time=1,443.029..1,443.029 rows=0 loops=1)

  • Recheck Cond: ((stay >= 3) AND (stay <= 15))
  • Filter: (destination_code = ANY ('{P-00000002,P-00000005,P-00000006,P-00000015,P-00000029,P-000000296,P-000000318,P-000000416,P-00000045,P-00000046,P-000000480,P-000000500,P-000000553,P-000000555,P-000000584,P-000000585,P-000000586,P-00000060,P-000000608,P-00000064,P-000000647,P-00000079,P-00000098,P-00000116,P-00000151,P-00000153,P-000001574,P-00000160,P-00000162,P-00000163,P-00000171,P-00000187,P-00000194,P-00000197,P-00000201,P-00000203,P-00000205,P-00000220,P-00000221,P-00000225,P-00000254,P-00000279,P-000002845,P-000003748,P-000003749,P-000003750,P-000007975,P-000007977,P-000008082,P-000008097,P-000008217,P-000008243,P-000008307,P-000008335,P-000008364,P-000008385,P-000008388,P-000008402,P-000008430,P-000008432,P-000008443,P-000008445,P-000008451,P-000008481,P-000008891,P-000008913,P-000008928,P-000008932,P-000008934,P-000008938,P-000008941,P-000009034,P-000009128,P-000009280,P-000009283,P-000009323,P-000009324,P-000009367,P-000009368,P-000009399,P-000011133,P-000011179,P-000011522,P-000011525,P-000012584,P-000012586,P-000012697,P-000012759,P-000019975,P-000020447,P-000022047,P-000048944,P-000051316,P-000051318,P-000051319,P-000053749,P-000054196,P-000063388,P-000070722,P-000070748,P-000079934,P-000080223,P-000084142,P-000084329,P-000084344,P-000084690,P-000087501,P-000102292,P-000102331,P-000112827,P-000125209,P-000126911,P-000126913,P-000126923,P-000128175,P-000128179,P-000131523,P-000139216,P-000139229,P-000139230,P-000139236,P-000141933,P-000142893,P-000143198,P-000148356,P-000152118,P-000157686,P-000159687,P-000159899,P-000168754,P-000168755,P-000176977,P-000176978,P-000177140,P-000177968,P-000178095,P-000179791,P-000181303,P-000182874,P-000182875,P-000182914,P-000182931,P-000182993,P-000183005,P-000184050,P-000186963,P-000186964,P-000186969,P-000186972,P-000186974,P-000187226,P-000187230,P-000187535,P-000189266,P-000189767,P-000192058,P-000192207}'::text[]))
  • Rows Removed by Filter: 551805
  • Heap Blocks: exact=4069
6. 52.928 52.928 ↓ 178.5 551,805 1

Bitmap Index Scan on ix_departures_stay (cost=0.00..15,701.77 rows=3,091 width=0) (actual time=52.928..52.928 rows=551,805 loops=1)

  • Index Cond: ((stay >= 3) AND (stay <= 15))
7. 40.175 54.626 ↓ 9.1 118,757 1

Bitmap Heap Scan on accommodation_departures (cost=863.64..27,845.55 rows=13,121 width=5) (actual time=16.565..54.626 rows=118,757 loops=1)

  • Recheck Cond: ((destination_code = ANY ('{P-00000002,P-00000005,P-00000006,P-00000015,P-00000029,P-000000296,P-000000318,P-000000416,P-00000045,P-00000046,P-000000480,P-000000500,P-000000553,P-000000555,P-000000584,P-000000585,P-000000586,P-00000060,P-000000608,P-00000064,P-000000647,P-00000079,P-00000098,P-00000116,P-00000151,P-00000153,P-000001574,P-00000160,P-00000162,P-00000163,P-00000171,P-00000187,P-00000194,P-00000197,P-00000201,P-00000203,P-00000205,P-00000220,P-00000221,P-00000225,P-00000254,P-00000279,P-000002845,P-000003748,P-000003749,P-000003750,P-000007975,P-000007977,P-000008082,P-000008097,P-000008217,P-000008243,P-000008307,P-000008335,P-000008364,P-000008385,P-000008388,P-000008402,P-000008430,P-000008432,P-000008443,P-000008445,P-000008451,P-000008481,P-000008891,P-000008913,P-000008928,P-000008932,P-000008934,P-000008938,P-000008941,P-000009034,P-000009128,P-000009280,P-000009283,P-000009323,P-000009324,P-000009367,P-000009368,P-000009399,P-000011133,P-000011179,P-000011522,P-000011525,P-000012584,P-000012586,P-000012697,P-000012759,P-000019975,P-000020447,P-000022047,P-000048944,P-000051316,P-000051318,P-000051319,P-000053749,P-000054196,P-000063388,P-000070722,P-000070748,P-000079934,P-000080223,P-000084142,P-000084329,P-000084344,P-000084690,P-000087501,P-000102292,P-000102331,P-000112827,P-000125209,P-000126911,P-000126913,P-000126923,P-000128175,P-000128179,P-000131523,P-000139216,P-000139229,P-000139230,P-000139236,P-000141933,P-000142893,P-000143198,P-000148356,P-000152118,P-000157686,P-000159687,P-000159899,P-000168754,P-000168755,P-000176977,P-000176978,P-000177140,P-000177968,P-000178095,P-000179791,P-000181303,P-000182874,P-000182875,P-000182914,P-000182931,P-000182993,P-000183005,P-000184050,P-000186963,P-000186964,P-000186969,P-000186972,P-000186974,P-000187226,P-000187230,P-000187535,P-000189266,P-000189767,P-000192058,P-000192207}'::text[])) AND (stay >= 3) AND (stay <= 15))
  • Heap Blocks: exact=15782
8. 14.451 14.451 ↓ 9.1 118,757 1

Bitmap Index Scan on ix_accommodation_departues_stay (cost=0.00..860.36 rows=13,121 width=0) (actual time=14.451..14.451 rows=118,757 loops=1)

  • Index Cond: ((destination_code = ANY ('{P-00000002,P-00000005,P-00000006,P-00000015,P-00000029,P-000000296,P-000000318,P-000000416,P-00000045,P-00000046,P-000000480,P-000000500,P-000000553,P-000000555,P-000000584,P-000000585,P-000000586,P-00000060,P-000000608,P-00000064,P-000000647,P-00000079,P-00000098,P-00000116,P-00000151,P-00000153,P-000001574,P-00000160,P-00000162,P-00000163,P-00000171,P-00000187,P-00000194,P-00000197,P-00000201,P-00000203,P-00000205,P-00000220,P-00000221,P-00000225,P-00000254,P-00000279,P-000002845,P-000003748,P-000003749,P-000003750,P-000007975,P-000007977,P-000008082,P-000008097,P-000008217,P-000008243,P-000008307,P-000008335,P-000008364,P-000008385,P-000008388,P-000008402,P-000008430,P-000008432,P-000008443,P-000008445,P-000008451,P-000008481,P-000008891,P-000008913,P-000008928,P-000008932,P-000008934,P-000008938,P-000008941,P-000009034,P-000009128,P-000009280,P-000009283,P-000009323,P-000009324,P-000009367,P-000009368,P-000009399,P-000011133,P-000011179,P-000011522,P-000011525,P-000012584,P-000012586,P-000012697,P-000012759,P-000019975,P-000020447,P-000022047,P-000048944,P-000051316,P-000051318,P-000051319,P-000053749,P-000054196,P-000063388,P-000070722,P-000070748,P-000079934,P-000080223,P-000084142,P-000084329,P-000084344,P-000084690,P-000087501,P-000102292,P-000102331,P-000112827,P-000125209,P-000126911,P-000126913,P-000126923,P-000128175,P-000128179,P-000131523,P-000139216,P-000139229,P-000139230,P-000139236,P-000141933,P-000142893,P-000143198,P-000148356,P-000152118,P-000157686,P-000159687,P-000159899,P-000168754,P-000168755,P-000176977,P-000176978,P-000177140,P-000177968,P-000178095,P-000179791,P-000181303,P-000182874,P-000182875,P-000182914,P-000182931,P-000182993,P-000183005,P-000184050,P-000186963,P-000186964,P-000186969,P-000186972,P-000186974,P-000187226,P-000187230,P-000187535,P-000189266,P-000189767,P-000192058,P-000192207}'::text[])) AND (stay >= 3) AND (stay <= 15))
9. 0.105 1,523.708 ↑ 37.2 418 1

Sort (cost=1,393.35..1,432.22 rows=15,547 width=5) (actual time=1,523.689..1,523.708 rows=418 loops=1)

  • Sort Key: cte.departure_date, cte.direct_flight DESC
  • Sort Method: quicksort Memory: 44kB
10. 1,523.603 1,523.603 ↑ 37.2 418 1

CTE Scan on cte (cost=0.00..310.94 rows=15,547 width=5) (actual time=1,523.392..1,523.603 rows=418 loops=1)

Planning time : 0.410 ms