#	exclusive	inclusive	rows x	rows	loops	node
1.	168745.781	168745.781	↓ 5.0	10	1	→ CTE Scan on with_articles wa (cost=12371.36..12373.92 rows=2 width=807) (actual time=168745.731..168745.781 rows=10 loops=1)
2.						CTE with_articles
3.	0.017	168745.730	↓ 5.0	10	1	→ Limit (cost=12371.35..12371.36 rows=2 width=2659) (actual time=168745.709..168745.730 rows=10 loops=1)
4.	8.175	168745.713	↓ 5.0	10	1	→ Sort (cost=12371.35..12371.36 rows=2 width=2659) (actual time=168745.706..168745.713 rows=10 loops=1) Sort Key: va.article_no, va.article_id Sort Method: top-N heapsort Memory: 32kB
5.	6.953	168737.538	↓ 638.0	1276	1	→ Nested Loop Left Join (cost=10186.55..12371.34 rows=2 width=2659) (actual time=348.122..168737.538 rows=1276 loops=1)
6.	6.243	168712.721	↓ 1276.0	1276	1	→ Nested Loop (cost=10186.55..12366.60 rows=1 width=174) (actual time=348.103..168712.721 rows=1276 loops=1)
7.	10.202	168697.546	↓ 1276.0	1276	1	→ Nested Loop Left Join (cost=10186.55..12366.32 rows=1 width=171) (actual time=348.089..168697.546 rows=1276 loops=1)
8.	38513.287	168663.100	↓ 1276.0	1276	1	→ Nested Loop Semi Join (cost=10186.55..12366.03 rows=1 width=163) (actual time=348.067..168663.100 rows=1276 loops=1) Join Filter: (va.article_id = atag.article_id)
9.	7.666	183.138	↓ 1277.0	1277	1	→ Nested Loop (cost=10124.10..10298.53 rows=1 width=171) (actual time=140.313..183.138 rows=1277 loops=1)
10.	5.588	163.979	↓ 1277.0	1277	1	→ Nested Loop (cost=10124.10..10290.24 rows=1 width=8) (actual time=140.304..163.979 rows=1277 loops=1)
11.	4.045	143.067	↓ 63.9	1277	1	→ HashAggregate (cost=10124.10..10124.30 rows=20 width=4) (actual time=140.284..143.067 rows=1277 loops=1)
12.	3.377	139.022	↓ 63.9	1277	1	→ HashAggregate (cost=10123.55..10123.85 rows=20 width=34) (actual time=137.887..139.022 rows=1277 loops=1)
13.	6.225	135.645	↓ 76.3	1527	1	→ Nested Loop (cost=2056.60..10123.40 rows=20 width=34) (actual time=13.920..135.645 rows=1527 loops=1)
14.	5.460	123.312	↓ 76.3	1527	1	→ Nested Loop (cost=2056.60..9970.29 rows=20 width=38) (actual time=13.907..123.312 rows=1527 loops=1)
15.	41.340	108.690	↓ 16.2	1527	1	→ Hash Join (cost=2056.60..9517.11 rows=94 width=12) (actual time=13.889..108.690 rows=1527 loops=1) Hash Cond: (o.object_id = otf.object_id)
16.	54.841	64.241	↓ 1.0	46888	1	→ Bitmap Heap Scan on objects o (cost=1465.73..8588.92 rows=44851 width=8) (actual time=10.034..64.241 rows=46888 loops=1) Recheck Cond: ((object_type_handle)::text = 'article'::text) Filter: ((object_state_handle)::text <> 'deleted'::text)
17.	9.400	9.400	↓ 1.7	77493	1	→ Bitmap Index Scan on ix_objects_object_type_handle (cost=0.00..1454.52 rows=45079 width=0) (actual time=9.400..9.400 rows=77493 loops=1) Index Cond: ((object_type_handle)::text = 'article'::text)
18.	1.368	3.109	↓ 1.0	1527	1	→ Hash (cost=572.32..572.32 rows=1484 width=4) (actual time=3.109..3.109 rows=1527 loops=1)
19.	1.478	1.741	↓ 1.0	1527	1	→ Bitmap Heap Scan on objects_to_frontends otf (cost=71.77..572.32 rows=1484 width=4) (actual time=0.278..1.741 rows=1527 loops=1) Recheck Cond: ((frontend_handle)::text = 'shop_collectors'::text)
20.	0.263	0.263	↓ 1.0	1536	1	→ Bitmap Index Scan on fk_objects_to_frontends_frontend_handle (cost=0.00..71.40 rows=1484 width=0) (actual time=0.263..0.263 rows=1536 loops=1) Index Cond: ((frontend_handle)::text = 'shop_collectors'::text)
21.	9.162	9.162	↑ 2.0	1	1527	→ Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl (cost=0.00..4.80 rows=2 width=34) (actual time=0.005..0.006 rows=1 loops=1527) Index Cond: (adl.object_revision_id = o.object_revision_id) Filter: (adl.language_id = 132)
22.	6.108	6.108	↑ 1.0	1	1527	→ Index Scan using pk_mv_v_articles on mv_v_articles va (cost=0.00..7.64 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1527) Index Cond: (va.article_id = otf.object_id)
23.	15.324	15.324	↑ 1.0	1	1277	→ Index Scan using fk_objects_to_frontends_object_id on objects_to_frontends obf (cost=0.00..8.28 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1277) Index Cond: (obf.object_id = (max(va.article_id))) Filter: ((obf.frontend_handle)::text = 'shop_collectors'::text)
24.	11.493	11.493	↑ 1.0	1	1277	→ Index Scan using pk_mv_v_articles on mv_v_articles va (cost=0.00..8.28 rows=1 width=163) (actual time=0.007..0.009 rows=1 loops=1277) Index Cond: (va.article_id = obf.object_id)
25.	83368.796	129966.675	↑ 1.2	37838	1277	→ Hash Join (cost=62.45..1535.39 rows=44676 width=4) (actual time=0.011..101.775 rows=37838 loops=1277) Hash Cond: (atag.article_group_id = mvagt.article_group_id)
26.	46596.453	46596.453	↑ 1.2	37838	1277	→ Seq Scan on articles_to_article_groups atag (cost=0.00..858.65 rows=44676 width=8) (actual time=0.008..36.489 rows=37838 loops=1277) Filter: ((article_group_assignment_type_handle)::text = 'default'::text)
27.	0.647	1.426	↑ 2.0	709	1	→ Hash (cost=44.73..44.73 rows=1418 width=4) (actual time=1.426..1.426 rows=709 loops=1)
28.	0.779	0.779	↑ 2.0	709	1	→ Seq Scan on mv_v_article_groups_tree mvagt (cost=0.00..44.73 rows=1418 width=4) (actual time=0.018..0.779 rows=709 loops=1) Filter: (tree_sort ~~ '01%'::text)
29.	24.244	24.244	↑ 1.0	1	1276	→ Index Scan using pk_packaging_units_lang on packaging_units_lang pul (cost=0.00..0.27 rows=1 width=13) (actual time=0.018..0.019 rows=1 loops=1276) Index Cond: (((va.packaging_unit_handle)::text = (pul.packaging_unit_handle)::text) AND (pul.language_id = 132))
30.	8.932	8.932	↑ 1.0	1	1276	→ Index Scan using fk_taxes_tax_id_object_state_handle on taxes t (cost=0.00..0.27 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=1276) Index Cond: (t.tax_id = va.vat_tax_id) Filter: ((t.object_state_handle)::text <> 'deleted'::text)
31.	17.864	17.864	↑ 2.0	1	1276	→ Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl (cost=0.00..4.72 rows=2 width=2489) (actual time=0.012..0.014 rows=1 loops=1276) Index Cond: (va.object_revision_id = adl.object_revision_id) Filter: (adl.language_id = 132)

                                                                                                                    QUERY PLAN                                                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on with_articles wa  (cost=12371.36..12373.92 rows=2 width=807) (actual time=168745.731..168745.781 rows=10 loops=1)
   CTE with_articles
     ->  Limit  (cost=12371.35..12371.36 rows=2 width=2659) (actual time=168745.709..168745.730 rows=10 loops=1)
           ->  Sort  (cost=12371.35..12371.36 rows=2 width=2659) (actual time=168745.706..168745.713 rows=10 loops=1)
                 Sort Key: va.article_no, va.article_id
                 Sort Method:  top-N heapsort  Memory: 32kB
                 ->  Nested Loop Left Join  (cost=10186.55..12371.34 rows=2 width=2659) (actual time=348.122..168737.538 rows=1276 loops=1)
                       ->  Nested Loop  (cost=10186.55..12366.60 rows=1 width=174) (actual time=348.103..168712.721 rows=1276 loops=1)
                             ->  Nested Loop Left Join  (cost=10186.55..12366.32 rows=1 width=171) (actual time=348.089..168697.546 rows=1276 loops=1)
                                   ->  Nested Loop Semi Join  (cost=10186.55..12366.03 rows=1 width=163) (actual time=348.067..168663.100 rows=1276 loops=1)
                                         Join Filter: (va.article_id = atag.article_id)
                                         ->  Nested Loop  (cost=10124.10..10298.53 rows=1 width=171) (actual time=140.313..183.138 rows=1277 loops=1)
                                               ->  Nested Loop  (cost=10124.10..10290.24 rows=1 width=8) (actual time=140.304..163.979 rows=1277 loops=1)
                                                     ->  HashAggregate  (cost=10124.10..10124.30 rows=20 width=4) (actual time=140.284..143.067 rows=1277 loops=1)
                                                           ->  HashAggregate  (cost=10123.55..10123.85 rows=20 width=34) (actual time=137.887..139.022 rows=1277 loops=1)
                                                                 ->  Nested Loop  (cost=2056.60..10123.40 rows=20 width=34) (actual time=13.920..135.645 rows=1527 loops=1)
                                                                       ->  Nested Loop  (cost=2056.60..9970.29 rows=20 width=38) (actual time=13.907..123.312 rows=1527 loops=1)
                                                                             ->  Hash Join  (cost=2056.60..9517.11 rows=94 width=12) (actual time=13.889..108.690 rows=1527 loops=1)
                                                                                   Hash Cond: (o.object_id = otf.object_id)
                                                                                   ->  Bitmap Heap Scan on objects o  (cost=1465.73..8588.92 rows=44851 width=8) (actual time=10.034..64.241 rows=46888 loops=1)
                                                                                         Recheck Cond: ((object_type_handle)::text = 'article'::text)
                                                                                         Filter: ((object_state_handle)::text <> 'deleted'::text)
                                                                                         ->  Bitmap Index Scan on ix_objects_object_type_handle  (cost=0.00..1454.52 rows=45079 width=0) (actual time=9.400..9.400 rows=77493 loops=1)
                                                                                               Index Cond: ((object_type_handle)::text = 'article'::text)
                                                                                   ->  Hash  (cost=572.32..572.32 rows=1484 width=4) (actual time=3.109..3.109 rows=1527 loops=1)
                                                                                         ->  Bitmap Heap Scan on objects_to_frontends otf  (cost=71.77..572.32 rows=1484 width=4) (actual time=0.278..1.741 rows=1527 loops=1)
                                                                                               Recheck Cond: ((frontend_handle)::text = 'shop_collectors'::text)
                                                                                               ->  Bitmap Index Scan on fk_objects_to_frontends_frontend_handle  (cost=0.00..71.40 rows=1484 width=0) (actual time=0.263..0.263 rows=1536 loops=1)
                                                                                                     Index Cond: ((frontend_handle)::text = 'shop_collectors'::text)
                                                                             ->  Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl  (cost=0.00..4.80 rows=2 width=34) (actual time=0.005..0.006 rows=1 loops=1527)
                                                                                   Index Cond: (adl.object_revision_id = o.object_revision_id)
                                                                                   Filter: (adl.language_id = 132)
                                                                       ->  Index Scan using pk_mv_v_articles on mv_v_articles va  (cost=0.00..7.64 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1527)
                                                                             Index Cond: (va.article_id = otf.object_id)
                                                     ->  Index Scan using fk_objects_to_frontends_object_id on objects_to_frontends obf  (cost=0.00..8.28 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1277)
                                                           Index Cond: (obf.object_id = (max(va.article_id)))
                                                           Filter: ((obf.frontend_handle)::text = 'shop_collectors'::text)
                                               ->  Index Scan using pk_mv_v_articles on mv_v_articles va  (cost=0.00..8.28 rows=1 width=163) (actual time=0.007..0.009 rows=1 loops=1277)
                                                     Index Cond: (va.article_id = obf.object_id)
                                         ->  Hash Join  (cost=62.45..1535.39 rows=44676 width=4) (actual time=0.011..101.775 rows=37838 loops=1277)
                                               Hash Cond: (atag.article_group_id = mvagt.article_group_id)
                                               ->  Seq Scan on articles_to_article_groups atag  (cost=0.00..858.65 rows=44676 width=8) (actual time=0.008..36.489 rows=37838 loops=1277)
                                                     Filter: ((article_group_assignment_type_handle)::text = 'default'::text)
                                               ->  Hash  (cost=44.73..44.73 rows=1418 width=4) (actual time=1.426..1.426 rows=709 loops=1)
                                                     ->  Seq Scan on mv_v_article_groups_tree mvagt  (cost=0.00..44.73 rows=1418 width=4) (actual time=0.018..0.779 rows=709 loops=1)
                                                           Filter: (tree_sort ~~ '01%'::text)
                                   ->  Index Scan using pk_packaging_units_lang on packaging_units_lang pul  (cost=0.00..0.27 rows=1 width=13) (actual time=0.018..0.019 rows=1 loops=1276)
                                         Index Cond: (((va.packaging_unit_handle)::text = (pul.packaging_unit_handle)::text) AND (pul.language_id = 132))
                             ->  Index Scan using fk_taxes_tax_id_object_state_handle on taxes t  (cost=0.00..0.27 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=1276)
                                   Index Cond: (t.tax_id = va.vat_tax_id)
                                   Filter: ((t.object_state_handle)::text <> 'deleted'::text)
                       ->  Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl  (cost=0.00..4.72 rows=2 width=2489) (actual time=0.012..0.014 rows=1 loops=1276)
                             Index Cond: (va.object_revision_id = adl.object_revision_id)
                             Filter: (adl.language_id = 132)
 Total runtime: 168746.043 ms
(55 rows)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	56.319 ms	0.0 %
Bitmap Index Scan	2	9.663 ms	0.0 %
CTE Scan	1	168745.781 ms	100.0 %
Hash	2	2.015 ms	0.0 %
Hash Join	2	83410.136 ms	49.4 %
HashAggregate	2	7.422 ms	0.0 %
Index Scan	7	93.127 ms	0.1 %
Limit	1	0.017 ms	0.0 %
Nested Loop	5	31.182 ms	0.0 %
Nested Loop Left Join	2	17.155 ms	0.0 %
Nested Loop Semi Join	1	38513.287 ms	22.8 %
Seq Scan	2	46597.232 ms	27.6 %
Sort	1	8.175 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
article_data_lang	2	27.026 ms	0.0 %
Index Scan	2	27.026 ms	100.0 %
articles_to_article_groups	1	46596.453 ms	27.6 %
Seq Scan	1	46596.453 ms	100.0 %
mv_v_article_groups_tree	1	0.779 ms	0.0 %
Seq Scan	1	0.779 ms	100.0 %
mv_v_articles	2	17.601 ms	0.0 %
Index Scan	2	17.601 ms	100.0 %
objects	1	54.841 ms	0.0 %
Bitmap Heap Scan	1	54.841 ms	100.0 %
objects_to_frontends	2	16.802 ms	0.0 %
Bitmap Heap Scan	1	1.478 ms	8.8 %
Index Scan	1	15.324 ms	91.2 %
packaging_units_lang	1	24.244 ms	0.0 %
Index Scan	1	24.244 ms	100.0 %
taxes	1	8.932 ms	0.0 %
Index Scan	1	8.932 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Jrf

Colorize:

Columns visibility:

Per node type stats

Per table stats