explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6an5

Settings
# exclusive inclusive rows x rows loops node
1. 49.378 942.068 ↑ 1.0 1 1

Aggregate (cost=66,475.54..66,475.55 rows=1 width=64) (actual time=942.068..942.068 rows=1 loops=1)

2.          

CTE values

3. 570.043 570.043 ↑ 1.2 11,012 1

CTE Scan on advertisements (cost=65,900.99..66,156.33 rows=12,767 width=3,099) (actual time=179.160..570.043 rows=11,012 loops=1)

4.          

CTE advertisements

5. 42.184 540.921 ↑ 1.2 11,012 1

Nested Loop Left Join (cost=12,808.20..65,900.99 rows=12,767 width=1,542) (actual time=179.153..540.921 rows=11,012 loops=1)

6. 13.621 465.701 ↑ 1.2 11,012 1

Hash Left Join (cost=12,807.78..52,498.74 rows=12,767 width=880) (actual time=179.110..465.701 rows=11,012 loops=1)

  • Hash Cond: ((advertisements_with_deleted.brand)::text = (brands.name)::text)
7. 3.303 450.788 ↑ 1.2 11,012 1

Nested Loop (cost=12,744.93..52,257.88 rows=12,767 width=876) (actual time=177.796..450.788 rows=11,012 loops=1)

8. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: (id = 12)
  • Rows Removed by Filter: 45
9. 13.823 447.469 ↑ 1.2 11,012 1

Nested Loop Left Join (cost=12,744.93..52,128.64 rows=12,767 width=360) (actual time=177.781..447.469 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))
10. 45.519 389.598 ↑ 1.2 11,012 1

Hash Left Join (cost=12,744.51..45,102.90 rows=12,871 width=364) (actual time=177.756..389.598 rows=11,012 loops=1)

  • Hash Cond: (advertisements_with_deleted.product_version_id = product_versions_with_deleted.id)
11. 8.250 172.949 ↑ 1.2 11,012 1

Hash Join (cost=592.54..30,618.78 rows=12,871 width=356) (actual time=6.580..172.949 rows=11,012 loops=1)

  • Hash Cond: (advertisements_with_deleted.merchant_id = merchants.id)
12. 160.384 164.522 ↑ 1.4 11,012 1

Bitmap Heap Scan on advertisements_with_deleted (cost=583.77..30,422.09 rows=15,793 width=345) (actual time=6.392..164.522 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
13. 4.138 4.138 ↓ 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.138..4.138 rows=24,883 loops=1)

  • Index Cond: ((category_id = 12) AND (deleted_at IS NULL))
14. 0.076 0.177 ↑ 1.0 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.101 0.101 ↑ 1.0 207 1

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

  • Filter: (country_id = 1)
  • Rows Removed by Filter: 56
16. 70.477 171.130 ↑ 1.0 247,489 1

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

  • Buckets: 16384 Batches: 4 Memory Usage: 2433kB
17. 100.653 100.653 ↑ 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.008..100.653 rows=247,489 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 110435
18. 44.048 44.048 ↑ 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.004 rows=1 loops=11,012)

  • Index Cond: (id = product_versions_with_deleted.product_id)
  • Filter: (deleted_at IS NULL)
19. 0.655 1.292 ↑ 1.0 2,082 1

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

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

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

21. 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)
22. 301.272 892.690 ↑ 1.0 1 1

Aggregate (cost=319.18..319.19 rows=1 width=24) (actual time=892.689..892.690 rows=1 loops=1)

23. 591.418 591.418 ↑ 1.2 11,012 1

CTE Scan on "values" (cost=0.00..255.34 rows=12,767 width=24) (actual time=179.187..591.418 rows=11,012 loops=1)

Planning time : 4.407 ms
Execution time : 955.022 ms