explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g9VJP : Slow lateral, trying to get just 1 row per product(id) while joining right products_displayproductaccess table, with different order by than distinct...

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 10,897.359 ↑ 1.0 100 1

Limit (cost=126,736,704.40..126,736,729.40 rows=100 width=942) (actual time=10,897.267..10,897.359 rows=100 loops=1)

2. 73.960 10,897.296 ↑ 520.5 100 1

Sort (cost=126,736,704.40..126,749,715.65 rows=52,045 width=942) (actual time=10,897.265..10,897.296 rows=100 loops=1)

  • Sort Key: products_displayproductaccess_753.is_instock DESC, products_displayproductaccess_753.price DESC
  • Sort Method: top-N heapsort Memory: 108kB
3. 107.480 10,823.336 ↑ 1.0 51,151 1

Nested Loop (cost=2,431.00..126,537,792.15 rows=52,045 width=942) (actual time=0.250..10,823.336 rows=51,151 loops=1)

4. 46.631 46.631 ↑ 1.0 52,045 1

Seq Scan on products_displayproduct (cost=0.00..2,189.45 rows=52,045 width=718) (actual time=0.098..46.631 rows=52,045 loops=1)

5. 52.045 10,669.225 ↑ 1.0 1 52,045

Limit (cost=2,431.00..2,431.25 rows=1 width=217) (actual time=0.205..0.205 rows=1 loops=52,045)

6. 1,717.485 10,617.180 ↑ 70.0 1 52,045

Sort (cost=2,431.00..2,448.50 rows=70 width=217) (actual time=0.204..0.204 rows=1 loops=52,045)

  • Sort Key: products_displayproductaccess_753.is_instock DESC, products_displayproductaccess_753.price DESC
  • Sort Method: top-N heapsort Memory: 25kB
7. 2,342.025 8,899.695 ↑ 1.0 67 52,045

Append (cost=42.50..2,396.00 rows=70 width=217) (actual time=0.007..0.171 rows=67 loops=52,045)

8. 988.855 988.855 ↑ 1.0 4 52,045

Index Scan using products_displayproductaccess_753_product_id_idx on products_displayproductaccess_753 (cost=42.50..337.82 rows=4 width=217) (actual time=0.006..0.019 rows=4 loops=52,045)

  • Index Cond: (product_id = products_displayproduct.id)
  • Filter: ((location_intspace = ANY ('{753,754,755,756,757}'::integer[])) AND (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[])))
  • Rows Removed by Filter: 10
9. 1,197.035 1,197.035 ↑ 1.0 12 52,045

Index Scan using products_displayproductaccess_754_product_id_idx on products_displayproductaccess_754 (cost=42.50..416.22 rows=12 width=217) (actual time=0.006..0.023 rows=12 loops=52,045)

  • Index Cond: (product_id = products_displayproduct.id)
  • Filter: ((location_intspace = ANY ('{753,754,755,756,757}'::integer[])) AND (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[])))
  • Rows Removed by Filter: 6
10. 2,029.755 2,029.755 ↑ 1.1 32 52,045

Index Scan using products_displayproductaccess_755_product_id_idx on products_displayproductaccess_755 (cost=42.75..789.19 rows=34 width=217) (actual time=0.006..0.039 rows=32 loops=52,045)

  • Index Cond: (product_id = products_displayproduct.id)
  • Filter: ((location_intspace = ANY ('{753,754,755,756,757}'::integer[])) AND (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[])))
  • Rows Removed by Filter: 4
11. 1,457.260 1,457.260 ↑ 1.1 16 52,045

Index Scan using products_displayproductaccess_756_product_id_idx on products_displayproductaccess_756 (cost=42.75..553.83 rows=17 width=217) (actual time=0.006..0.028 rows=16 loops=52,045)

  • Index Cond: (product_id = products_displayproduct.id)
  • Filter: ((location_intspace = ANY ('{753,754,755,756,757}'::integer[])) AND (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[])))
  • Rows Removed by Filter: 8
12. 884.765 884.765 ↑ 1.0 3 52,045

Index Scan using products_displayproductaccess_757_product_id_idx on products_displayproductaccess_757 (cost=42.50..298.59 rows=3 width=217) (actual time=0.008..0.017 rows=3 loops=52,045)

  • Index Cond: (product_id = products_displayproduct.id)
  • Filter: ((location_intspace = ANY ('{753,754,755,756,757}'::integer[])) AND (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[])))
  • Rows Removed by Filter: 10
Planning time : 1.775 ms
Execution time : 10,897.486 ms