Result: 3Ll5

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
233.805	1584.175	↑ 1.2	8560	1	→ Nested Loop (cost=31785.53..265085.89 rows=9975 width=368) (actual time=1009.777..1584.175 rows=8560 loops=1) Total runtime: 1591.244 ms
16.828	1256.210	↑ 1.2	8560	1	→ Nested Loop Left Join (cost=31785.53..183855.18 rows=9967 width=146) (actual time=1009.340..1256.210 rows=8560 loops=1)
12.384	1179.896	↑ 1.2	8498	1	→ Hash Left Join (cost=31785.53..67493.52 rows=9967 width=142) (actual time=1009.307..1179.896 rows=8498 loops=1) Hash Cond: (products.product_group_id = product_groups.id)
16.641	1134.518	↑ 1.2	8498	1	→ Hash Join (cost=30611.63..66201.88 rows=9967 width=138) (actual time=976.219..1134.518 rows=8498 loops=1) Hash Cond: (products.company_id = companies.id)
16.827	423.480	↑ 1.2	8498	1	→ Hash Left Join (cost=10470.67..45861.58 rows=9967 width=117) (actual time=281.562..423.480 rows=8498 loops=1) Hash Cond: (products.rubric_id = rubrics.id)
22.864	254.568	↑ 1.2	8498	1	→ Hash Left Join (cost=3296.79..38489.39 rows=9967 width=120) (actual time=129.378..254.568 rows=8498 loops=1) Hash Cond: (products.rubric_id = rubrics_denormalization_tree.rubric_id)
103.984	106.946	↑ 1.2	8498	1	→ Bitmap Heap Scan on products (cost=215.54..35209.83 rows=9967 width=88) (actual time=4.512..106.946 rows=8498 loops=1) Recheck Cond: ((id >= 7740001) AND (id <= 7750000)) Filter: (CASE WHEN (state = 'pending'::product_state) THEN 1 WHEN (state = 'accepted'::product_state) THEN 2 WHEN (state = 'changed'::product_state) THEN 3 WHEN (state = 'declined'::product_state) THEN 4 WHEN (state = 'deleted'::product_state) THEN 5 ELSE NULL::integer END <> 5)
2.962	2.962	↑ 1.0	10010	1	→ Bitmap Index Scan on products_436_pkey (cost=0.00..213.05 rows=10017 width=0) (actual time=2.962..2.962 rows=10010 loops=1) Index Cond: ((id >= 7740001) AND (id <= 7750000))
66.091	124.758	↑ 1.0	103211	1	→ Hash (cost=1791.11..1791.11 rows=103211 width=32) (actual time=124.758..124.758 rows=103211 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 6451kB
58.667	58.667	↑ 1.0	103211	1	→ Seq Scan on rubrics_denormalization_tree (cost=0.00..1791.11 rows=103211 width=32) (actual time=0.010..58.667 rows=103211 loops=1)
53.884	152.085	↑ 1.0	103906	1	→ Hash (cost=5875.06..5875.06 rows=103906 width=5) (actual time=152.085..152.085 rows=103906 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 4059kB
98.201	98.201	↑ 1.0	103906	1	→ Seq Scan on rubrics (cost=0.00..5875.06 rows=103906 width=5) (actual time=0.046..98.201 rows=103906 loops=1)
262.660	694.397	↑ 1.0	372798	1	→ Hash (cost=15480.98..15480.98 rows=372798 width=25) (actual time=694.397..694.397 rows=372798 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 23228kB
431.737	431.737	↑ 1.0	372798	1	→ Seq Scan on companies (cost=0.00..15480.98 rows=372798 width=25) (actual time=0.000..431.737 rows=372798 loops=1)
17.186	32.994	↑ 1.0	26428	1	→ Hash (cost=839.51..839.51 rows=26751 width=8) (actual time=32.994..32.994 rows=26428 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 1033kB
15.808	15.808	↑ 1.0	26428	1	→ Seq Scan on product_groups (cost=0.00..839.51 rows=26751 width=8) (actual time=0.009..15.808 rows=26428 loops=1)
59.486	59.486	↑ 5.0	1	8498	→ Index Scan using i_product_images_product_id on product_images (cost=0.00..11.61 rows=5 width=8) (actual time=0.006..0.007 rows=1 loops=8498) Index Cond: (product_images.product_id = products.id) Filter: (product_images."position" = 1)
94.160	94.160	↑ 1.0	1	8560	→ Index Scan using index_search_index_products_on_product_id on search_index_products (cost=0.00..7.87 rows=1 width=226) (actual time=0.010..0.011 rows=1 loops=8560) Index Cond: (search_index_products.product_id = products.id)

Nested Loop  (cost=31785.53..265085.89 rows=9975 width=368) (actual time=1009.777..1584.175 rows=8560 loops=1)
   ->  Nested Loop Left Join  (cost=31785.53..183855.18 rows=9967 width=146) (actual time=1009.340..1256.210 rows=8560 loops=1)
         ->  Hash Left Join  (cost=31785.53..67493.52 rows=9967 width=142) (actual time=1009.307..1179.896 rows=8498 loops=1)
               Hash Cond: (products.product_group_id = product_groups.id)
               ->  Hash Join  (cost=30611.63..66201.88 rows=9967 width=138) (actual time=976.219..1134.518 rows=8498 loops=1)
                     Hash Cond: (products.company_id = companies.id)
                     ->  Hash Left Join  (cost=10470.67..45861.58 rows=9967 width=117) (actual time=281.562..423.480 rows=8498 loops=1)
                           Hash Cond: (products.rubric_id = rubrics.id)
                           ->  Hash Left Join  (cost=3296.79..38489.39 rows=9967 width=120) (actual time=129.378..254.568 rows=8498 loops=1)
                                 Hash Cond: (products.rubric_id = rubrics_denormalization_tree.rubric_id)
                                 ->  Bitmap Heap Scan on products  (cost=215.54..35209.83 rows=9967 width=88) (actual time=4.512..106.946 rows=8498 loops=1)
                                       Recheck Cond: ((id >= 7740001) AND (id <= 7750000))
                                       Filter: (CASE WHEN (state = 'pending'::product_state) THEN 1 WHEN (state = 'accepted'::product_state) THEN 2 WHEN (state = 'changed'::product_state) THEN 3 WHEN (state = 'declined'::product_state) THEN 4 WHEN (state = 'deleted'::product_state) THEN 5 ELSE NULL::integer END <> 5)
                                       ->  Bitmap Index Scan on products_436_pkey  (cost=0.00..213.05 rows=10017 width=0) (actual time=2.962..2.962 rows=10010 loops=1)
                                             Index Cond: ((id >= 7740001) AND (id <= 7750000))
                                 ->  Hash  (cost=1791.11..1791.11 rows=103211 width=32) (actual time=124.758..124.758 rows=103211 loops=1)
                                       Buckets: 16384  Batches: 1  Memory Usage: 6451kB
                                       ->  Seq Scan on rubrics_denormalization_tree  (cost=0.00..1791.11 rows=103211 width=32) (actual time=0.010..58.667 rows=103211 loops=1)
                           ->  Hash  (cost=5875.06..5875.06 rows=103906 width=5) (actual time=152.085..152.085 rows=103906 loops=1)
                                 Buckets: 16384  Batches: 1  Memory Usage: 4059kB
                                 ->  Seq Scan on rubrics  (cost=0.00..5875.06 rows=103906 width=5) (actual time=0.046..98.201 rows=103906 loops=1)
                     ->  Hash  (cost=15480.98..15480.98 rows=372798 width=25) (actual time=694.397..694.397 rows=372798 loops=1)
                           Buckets: 65536  Batches: 1  Memory Usage: 23228kB
                           ->  Seq Scan on companies  (cost=0.00..15480.98 rows=372798 width=25) (actual time=0.000..431.737 rows=372798 loops=1)
               ->  Hash  (cost=839.51..839.51 rows=26751 width=8) (actual time=32.994..32.994 rows=26428 loops=1)
                     Buckets: 4096  Batches: 1  Memory Usage: 1033kB
                     ->  Seq Scan on product_groups  (cost=0.00..839.51 rows=26751 width=8) (actual time=0.009..15.808 rows=26428 loops=1)
         ->  Index Scan using i_product_images_product_id on product_images  (cost=0.00..11.61 rows=5 width=8) (actual time=0.006..0.007 rows=1 loops=8498)
               Index Cond: (product_images.product_id = products.id)
               Filter: (product_images."position" = 1)
   ->  Index Scan using index_search_index_products_on_product_id on search_index_products  (cost=0.00..7.87 rows=1 width=226) (actual time=0.010..0.011 rows=1 loops=8560)
         Index Cond: (search_index_products.product_id = products.id)
 Total runtime: 1591.244 ms
(33 rows)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	103.984 ms	6.6 %
Bitmap Index Scan	1	2.962 ms	0.2 %
Hash	4	399.821 ms	25.2 %
Hash Join	1	16.641 ms	1.1 %
Hash Left Join	3	52.075 ms	3.3 %
Index Scan	2	153.646 ms	9.7 %
Nested Loop	1	233.805 ms	14.8 %
Nested Loop Left Join	1	16.828 ms	1.1 %
Seq Scan	4	604.413 ms	38.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
companies	1	431.737 ms	27.3 %
Seq Scan	1	431.737 ms	100.0 %
product_groups	1	15.808 ms	1.0 %
Seq Scan	1	15.808 ms	100.0 %
product_images	1	59.486 ms	3.8 %
Index Scan	1	59.486 ms	100.0 %
products	1	103.984 ms	6.6 %
Bitmap Heap Scan	1	103.984 ms	100.0 %
rubrics	1	98.201 ms	6.2 %
Seq Scan	1	98.201 ms	100.0 %
rubrics_denormalization_tree	1	58.667 ms	3.7 %
Seq Scan	1	58.667 ms	100.0 %
search_index_products	1	94.160 ms	5.9 %
Index Scan	1	94.160 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 3Ll5

Colorize:

Columns visibility:

Per node type stats

Per table stats