explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fktGm

Settings
# exclusive inclusive rows x rows loops node
1. 54.509 1,956.871 ↑ 1.0 1 1

Aggregate (cost=115,054.96..115,054.98 rows=1 width=64) (actual time=1,956.870..1,956.871 rows=1 loops=1)

2.          

CTE values

3. 1,533.566 1,533.566 ↑ 7.2 11,012 1

CTE Scan on advertisements (cost=111,467.04..113,061.66 rows=79,731 width=3,131) (actual time=1,345.215..1,533.566 rows=11,012 loops=1)

4.          

CTE ad_page_urls

5. 292.806 292.806 ↑ 1.0 187,828 1

Seq Scan on ad_pages (cost=0.00..5,253.85 rows=187,828 width=45) (actual time=0.032..292.806 rows=187,828 loops=1)

6.          

CTE advertisements

7. 38.364 1,491.988 ↑ 7.2 11,012 1

Nested Loop (cost=98,463.41..106,213.19 rows=79,731 width=1,743) (actual time=1,345.209..1,491.988 rows=11,012 loops=1)

8. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on categories (cost=0.00..1.58 rows=1 width=520) (actual time=0.016..0.023 rows=1 loops=1)

  • Filter: (id = 12)
  • Rows Removed by Filter: 45
9. 30.134 1,453.601 ↑ 7.2 11,012 1

Merge Right Join (cost=98,463.41..100,630.44 rows=79,731 width=1,227) (actual time=1,345.143..1,453.601 rows=11,012 loops=1)

  • Merge Cond: (ad_page_urls.id = advertisements_with_deleted.ad_page_id)
10. 380.223 826.654 ↑ 1.0 185,064 1

Sort (cost=25,347.40..25,816.97 rows=187,828 width=36) (actual time=784.650..826.654 rows=185,064 loops=1)

  • Sort Key: ad_page_urls.id
  • Sort Method: external sort Disk: 19472kB
11. 446.431 446.431 ↑ 1.0 187,828 1

CTE Scan on ad_page_urls (cost=0.00..3,756.56 rows=187,828 width=36) (actual time=0.036..446.431 rows=187,828 loops=1)

12. 31.723 596.813 ↑ 1.2 11,012 1

Materialize (cost=73,116.00..73,179.87 rows=12,773 width=1,199) (actual time=547.676..596.813 rows=11,012 loops=1)

13. 74.583 565.090 ↑ 1.2 11,012 1

Sort (cost=73,116.00..73,147.94 rows=12,773 width=1,199) (actual time=547.668..565.090 rows=11,012 loops=1)

  • Sort Key: advertisements_with_deleted.ad_page_id
  • Sort Method: external merge Disk: 8480kB
14. 4.407 490.507 ↑ 1.2 11,012 1

Nested Loop Left Join (cost=12,808.20..65,563.33 rows=12,773 width=1,199) (actual time=183.682..490.507 rows=11,012 loops=1)

15. 11.307 453.064 ↑ 1.2 11,012 1

Hash Left Join (cost=12,807.78..52,921.16 rows=12,773 width=537) (actual time=183.663..453.064 rows=11,012 loops=1)

  • Hash Cond: ((advertisements_with_deleted.brand)::text = (brands.name)::text)
16. 15.130 440.663 ↑ 1.2 11,012 1

Nested Loop Left Join (cost=12,744.93..52,680.22 rows=12,773 width=533) (actual time=182.541..440.663 rows=11,012 loops=1)

  • Filter: (((NOT products_with_deleted.hidden) AND (NOT products_with_deleted.map_upp_only)) OR (advertisements_with_deleted.product_version_id IS NULL))
17. 47.196 392.497 ↑ 1.2 11,012 1

Hash Left Join (cost=12,744.51..45,654.90 rows=12,871 width=537) (actual time=182.453..392.497 rows=11,012 loops=1)

  • Hash Cond: (advertisements_with_deleted.product_version_id = product_versions_with_deleted.id)
18. 7.958 169.650 ↑ 1.2 11,012 1

Hash Join (cost=592.54..30,618.78 rows=12,871 width=529) (actual time=6.724..169.650 rows=11,012 loops=1)

  • Hash Cond: (advertisements_with_deleted.merchant_id = merchants.id)
19. 157.270 161.559 ↑ 1.4 11,012 1

Bitmap Heap Scan on advertisements_with_deleted (cost=583.77..30,422.09 rows=15,793 width=518) (actual time=6.574..161.559 rows=11,012 loops=1)

  • Recheck Cond: ((category_id = 12) AND (deleted_at IS NULL))
  • Filter: ((ad_date <= '2019-09-30'::date) AND (((ad_date >= '2017-01-01'::date) AND (ad_date <= '2019-08-13'::date)) OR ((end_date >= '2017-01-01'::date) AND (end_date <= '2019-08-13'::date)) OR (('2017-01-01'::date >= ad_date) AND ('2017-01-01'::date <= end_date)) OR (('2019-08-13'::date >= ad_date) AND ('2019-08-13'::date <= end_date))))
  • Rows Removed by Filter: 13871
  • Heap Blocks: exact=12113
20. 4.289 4.289 ↓ 1.1 24,883 1

Bitmap Index Scan on index_advertisements_on_category_id_and_deleted_at (cost=0.00..579.82 rows=22,740 width=0) (actual time=4.289..4.289 rows=24,883 loops=1)

  • Index Cond: ((category_id = 12) AND (deleted_at IS NULL))
21. 0.049 0.133 ↑ 1.0 207 1

Hash (cost=6.18..6.18 rows=207 width=15) (actual time=0.133..0.133 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.084 0.084 ↑ 1.0 207 1

Seq Scan on merchants (cost=0.00..6.18 rows=207 width=15) (actual time=0.025..0.084 rows=207 loops=1)

  • Filter: (country_id = 1)
  • Rows Removed by Filter: 56
23. 74.505 175.651 ↑ 1.0 247,489 1

Hash (cost=8,087.21..8,087.21 rows=247,741 width=8) (actual time=175.651..175.651 rows=247,489 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 2433kB
24. 101.146 101.146 ↑ 1.0 247,489 1

Seq Scan on product_versions_with_deleted (cost=0.00..8,087.21 rows=247,741 width=8) (actual time=0.019..101.146 rows=247,489 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 110435
25. 33.036 33.036 ↑ 1.0 1 11,012

Index Scan using products_pkey on products_with_deleted (cost=0.42..0.54 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=11,012)

  • Index Cond: (id = product_versions_with_deleted.product_id)
  • Filter: (deleted_at IS NULL)
26. 0.555 1.094 ↑ 1.0 2,082 1

Hash (cost=36.82..36.82 rows=2,082 width=13) (actual time=1.094..1.094 rows=2,082 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 92kB
27. 0.539 0.539 ↑ 1.0 2,082 1

Seq Scan on brands (cost=0.00..36.82 rows=2,082 width=13) (actual time=0.026..0.539 rows=2,082 loops=1)

28. 33.036 33.036 ↑ 1.0 1 11,012

Index Scan using index_specifications_on_product_version_id on specifications_with_deleted (cost=0.42..0.98 rows=1 width=670) (actual time=0.002..0.003 rows=1 loops=11,012)

  • Index Cond: (product_version_id = product_versions_with_deleted.id)
  • Filter: (deleted_at IS NULL)
29. 335.510 1,902.362 ↑ 1.0 1 1

Aggregate (cost=1,993.28..1,993.29 rows=1 width=24) (actual time=1,902.361..1,902.362 rows=1 loops=1)

30. 1,566.852 1,566.852 ↑ 7.2 11,012 1

CTE Scan on "values" (cost=0.00..1,594.62 rows=79,731 width=24) (actual time=1,345.232..1,566.852 rows=11,012 loops=1)

Planning time : 8.250 ms
Execution time : 1,981.567 ms