explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YpLR : Slow, just trying to get distinct rows from left table in a join, ordered by something other than the distinct.

Settings
# exclusive inclusive rows x rows loops node
1. 0.176 7,856.399 ↑ 1.0 100 1

Nested Loop (cost=42,775,624.42..42,778,740.42 rows=100 width=719) (actual time=7,855.712..7,856.399 rows=100 loops=1)

2. 0.062 7,855.723 ↑ 1.0 100 1

Limit (cost=42,775,595.42..42,775,620.42 rows=100 width=11) (actual time=7,855.627..7,855.723 rows=100 loops=1)

3. 21.718 7,855.661 ↑ 477.8 100 1

Sort (cost=42,775,595.42..42,787,540.67 rows=47,781 width=11) (actual time=7,855.625..7,855.661 rows=100 loops=1)

  • Sort Key: products_displayproductaccess_755.price DESC, products_displayproductaccess_755.is_instock DESC
  • Sort Method: top-N heapsort Memory: 32kB
4. 1,202.274 7,833.943 ↓ 1.1 51,151 1

Unique (cost=40,838,903.57..42,592,502.07 rows=47,781 width=11) (actual time=4,655.659..7,833.943 rows=51,151 loops=1)

5. 4,670.766 6,631.669 ↓ 1.0 3,508,394 1

Sort (cost=40,838,903.57..41,715,702.82 rows=3,507,197 width=11) (actual time=4,655.657..6,631.669 rows=3,508,394 loops=1)

  • Sort Key: products_displayproductaccess_755.product_id, products_displayproductaccess_755.is_instock DESC, products_displayproductaccess_755.price DESC
  • Sort Method: external merge Disk: 75512kB
6. 0.000 1,960.903 ↓ 1.0 3,508,394 1

Gather (cost=1,042.50..2,677,256.82 rows=3,507,197 width=11) (actual time=1.605..1,960.903 rows=3,508,394 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 636.096 2,181.325 ↑ 1.3 877,098 4 / 4

Parallel Append (cost=42.50..2,325,537.12 rows=1,131,354 width=11) (actual time=0.107..2,181.325 rows=877,098 loops=4)

8. 863.733 863.733 ↑ 2.4 419,886 4 / 4

Parallel Index Scan using products_displayproductaccess_755_location_id_idx on products_displayproductaccess_755 (cost=42.75..1,091,008.79 rows=987,436 width=11) (actual time=0.050..863.733 rows=419,886 loops=4)

  • Index Cond: (location_id = ANY ('{1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,89,1055,1056,604,47,50,51,52,606,55,58,59,60,61,62,64,65,66,68,69,70,97,72,73,75,76,78,79,81,594,83,84,597,598,599,600,601,602,603,92,605,94,607,608,609,610,611,612,101,102,104,617,618,108,109,624,113,114,627,116,118,631,632,633,634,635,636,637,639,130,131,133,134,620,621,149,150,151,155,626,1523,630,120,121,124,53,909,910,593,1519,99,932,934,935,937,947,948,949,950,951,952,953,954,955,973,974,975,999,1517,1518,613,1520,1521,1522,595,1524,1525,1526,1528,1530,1531,1532,1533,1534,85}'::integer[]))
  • Filter: (location_intspace = ANY ('{753,754,755,756,757}'::integer[]))
9. 350.213 350.213 ↑ 1.2 429,208 2 / 4

Parallel Index Scan using products_displayproductaccess_756_location_id_idx on products_displayproductaccess_756 (cost=42.75..565,053.00 rows=504,623 width=11) (actual time=0.054..700.426 rows=429,208 loops=2)

  • Index Cond: (location_id = ANY ('{1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,89,1055,1056,604,47,50,51,52,606,55,58,59,60,61,62,64,65,66,68,69,70,97,72,73,75,76,78,79,81,594,83,84,597,598,599,600,601,602,603,92,605,94,607,608,609,610,611,612,101,102,104,617,618,108,109,624,113,114,627,116,118,631,632,633,634,635,636,637,639,130,131,133,134,620,621,149,150,151,155,626,1523,630,120,121,124,53,909,910,593,1519,99,932,934,935,937,947,948,949,950,951,952,953,954,955,973,974,975,999,1517,1518,613,1520,1521,1522,595,1524,1525,1526,1528,1530,1531,1532,1533,1534,85}'::integer[]))
  • Filter: (location_intspace = ANY ('{753,754,755,756,757}'::integer[]))
10. 222.573 222.573 ↓ 1.7 622,077 1 / 4

Parallel Index Scan using products_displayproductaccess_754_location_id_idx on products_displayproductaccess_754 (cost=42.50..412,529.66 rows=366,336 width=11) (actual time=0.120..890.292 rows=622,077 loops=1)

  • Index Cond: (location_id = ANY ('{1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,89,1055,1056,604,47,50,51,52,606,55,58,59,60,61,62,64,65,66,68,69,70,97,72,73,75,76,78,79,81,594,83,84,597,598,599,600,601,602,603,92,605,94,607,608,609,610,611,612,101,102,104,617,618,108,109,624,113,114,627,116,118,631,632,633,634,635,636,637,639,130,131,133,134,620,621,149,150,151,155,626,1523,630,120,121,124,53,909,910,593,1519,99,932,934,935,937,947,948,949,950,951,952,953,954,955,973,974,975,999,1517,1518,613,1520,1521,1522,595,1524,1525,1526,1528,1530,1531,1532,1533,1534,85}'::integer[]))
  • Filter: (location_intspace = ANY ('{753,754,755,756,757}'::integer[]))
11. 64.309 64.309 ↓ 1.7 199,057 1 / 4

Parallel Index Scan using products_displayproductaccess_753_location_id_idx on products_displayproductaccess_753 (cost=42.50..141,703.89 rows=116,809 width=11) (actual time=0.165..257.238 rows=199,057 loops=1)

  • Index Cond: (location_id = ANY ('{1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,89,1055,1056,604,47,50,51,52,606,55,58,59,60,61,62,64,65,66,68,69,70,97,72,73,75,76,78,79,81,594,83,84,597,598,599,600,601,602,603,92,605,94,607,608,609,610,611,612,101,102,104,617,618,108,109,624,113,114,627,116,118,631,632,633,634,635,636,637,639,130,131,133,134,620,621,149,150,151,155,626,1523,630,120,121,124,53,909,910,593,1519,99,932,934,935,937,947,948,949,950,951,952,953,954,955,973,974,975,999,1517,1518,613,1520,1521,1522,595,1524,1525,1526,1528,1530,1531,1532,1533,1534,85}'::integer[]))
  • Filter: (location_intspace = ANY ('{753,754,755,756,757}'::integer[]))
12. 44.401 44.401 ↑ 1.2 74,650 2 / 4

Parallel Index Scan using products_displayproductaccess_757_location_id_idx on products_displayproductaccess_757 (cost=42.50..109,585.01 rows=87,853 width=11) (actual time=0.087..88.802 rows=74,650 loops=2)

  • Index Cond: (location_id = ANY ('{1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,89,1055,1056,604,47,50,51,52,606,55,58,59,60,61,62,64,65,66,68,69,70,97,72,73,75,76,78,79,81,594,83,84,597,598,599,600,601,602,603,92,605,94,607,608,609,610,611,612,101,102,104,617,618,108,109,624,113,114,627,116,118,631,632,633,634,635,636,637,639,130,131,133,134,620,621,149,150,151,155,626,1523,630,120,121,124,53,909,910,593,1519,99,932,934,935,937,947,948,949,950,951,952,953,954,955,973,974,975,999,1517,1518,613,1520,1521,1522,595,1524,1525,1526,1528,1530,1531,1532,1533,1534,85}'::integer[]))
  • Filter: (location_intspace = ANY ('{753,754,755,756,757}'::integer[]))
13. 0.500 0.500 ↑ 1.0 1 100

Index Scan using products_di_id_f3063a_idx on products_displayproduct p (cost=29.00..31.19 rows=1 width=712) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: (id = products_displayproductaccess_755.product_id)