Result: ztr

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
29.675	5913.565	↑ 1.0	1	1	→ Aggregate (cost=38881.42..38881.43 rows=1 width=0) (actual time=5913.565..5913.565 rows=1 loops=1)
179.163	5883.890	↓ 67456.8	269827	1	→ Nested Loop (cost=38787.96..38881.41 rows=4 width=0) (actual time=2050.525..5883.890 rows=269827 loops=1)
137.212	5165.073	↓ 67456.8	269827	1	→ Nested Loop (cost=38787.96..38852.06 rows=4 width=4) (actual time=2050.504..5165.073 rows=269827 loops=1)
135.387	3678.726	↓ 67456.8	269827	1	→ Nested Loop (cost=38787.96..38821.79 rows=4 width=8) (actual time=2050.495..3678.726 rows=269827 loops=1)
369.871	2194.204	↓ 67456.8	269827	1	→ HashAggregate (cost=38787.96..38788.00 rows=4 width=4) (actual time=2050.475..2194.204 rows=269827 loops=1)
266.353	1824.333	↓ 67456.8	269827	1	→ Nested Loop (cost=0.00..38787.95 rows=4 width=4) (actual time=0.211..1824.333 rows=269827 loops=1) Join Filter: (((public.orders.created_on)::date >= mos.start_date) AND ((public.orders.created_on)::date < mos.end_date))
67.235	478.672	↓ 7292.6	269827	1	→ Nested Loop (cost=0.00..38507.52 rows=37 width=16) (actual time=0.142..478.672 rows=269827 loops=1) Join Filter: (COALESCE(mos.quantity, li.quantity) = li.quantity)
21.155	32.382	↓ 23.4	1613	1	→ Nested Loop (cost=0.00..2243.93 rows=69 width=16) (actual time=0.094..32.382 rows=1613 loops=1) Join Filter: ((public.products.name)::text ~~* (('%'::text \|\| mos.search_term) \|\| '%'::text))
0.049	0.049	↑ 1.0	1	1	→ Index Scan using mc_order_searches_pkey on mc_order_searches mos (cost=0.00..8.27 rows=1 width=44) (actual time=0.047..0.049 rows=1 loops=1) Index Cond: (mc_order_search_id = 34)
11.178	11.178	↑ 1.0	13806	1	→ Seq Scan on products (cost=0.00..1994.06 rows=13806 width=41) (actual time=0.006..11.178 rows=13806 loops=1)
379.055	379.055	↑ 1.4	167	1613	→ Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..521.93 rows=242 width=16) (actual time=0.013..0.235 rows=167 loops=1613) Index Cond: (product_id = public.products.id) Filter: (((state)::text <> 'canceled'::text) AND ((state)::text <> 'canceled'::text))
1079.308	1079.308	↑ 1.0	1	269827	→ Index Scan using orders_pkey on orders (cost=0.00..7.56 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=269827) Index Cond: (id = li.order_id)
1349.135	1349.135	↑ 1.0	1	269827	→ Index Scan using line_items_pkey on line_items li (cost=0.00..8.43 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=269827) Index Cond: (id = li.id)
1349.135	1349.135	↑ 1.0	1	269827	→ Index Scan using orders_pkey on orders (cost=0.00..7.56 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=269827) Index Cond: (id = li.order_id)
539.654	539.654	↑ 1.0	1	269827	→ Index Scan using products_pkey on products (cost=0.00..7.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=269827) Index Cond: (id = li.product_id)

 Aggregate  (cost=38881.42..38881.43 rows=1 width=0) (actual time=5913.565..5913.565 rows=1 loops=1)
   ->  Nested Loop  (cost=38787.96..38881.41 rows=4 width=0) (actual time=2050.525..5883.890 rows=269827 loops=1)
         ->  Nested Loop  (cost=38787.96..38852.06 rows=4 width=4) (actual time=2050.504..5165.073 rows=269827 loops=1)
               ->  Nested Loop  (cost=38787.96..38821.79 rows=4 width=8) (actual time=2050.495..3678.726 rows=269827 loops=1)
                     ->  HashAggregate  (cost=38787.96..38788.00 rows=4 width=4) (actual time=2050.475..2194.204 rows=269827 loops=1)
                           ->  Nested Loop  (cost=0.00..38787.95 rows=4 width=4) (actual time=0.211..1824.333 rows=269827 loops=1)
                                 Join Filter: (((public.orders.created_on)::date >= mos.start_date) AND ((public.orders.created_on)::date < mos.end_date))
                                 ->  Nested Loop  (cost=0.00..38507.52 rows=37 width=16) (actual time=0.142..478.672 rows=269827 loops=1)
                                       Join Filter: (COALESCE(mos.quantity, li.quantity) = li.quantity)
                                       ->  Nested Loop  (cost=0.00..2243.93 rows=69 width=16) (actual time=0.094..32.382 rows=1613 loops=1)
                                             Join Filter: ((public.products.name)::text ~~* (('%'::text || mos.search_term) || '%'::text))
                                             ->  Index Scan using mc_order_searches_pkey on mc_order_searches mos  (cost=0.00..8.27 rows=1 width=44) (actual time=0.047..0.049 rows=1 loops=1)
                                                   Index Cond: (mc_order_search_id = 34)
                                             ->  Seq Scan on products  (cost=0.00..1994.06 rows=13806 width=41) (actual time=0.006..11.178 rows=13806 loops=1)
                                       ->  Index Scan using index_line_items_on_product_id on line_items li  (cost=0.00..521.93 rows=242 width=16) (actual time=0.013..0.235 rows=167 loops=1613)
                                             Index Cond: (product_id = public.products.id)
                                             Filter: (((state)::text <> 'canceled'::text) AND ((state)::text <> 'canceled'::text))
                                 ->  Index Scan using orders_pkey on orders  (cost=0.00..7.56 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=269827)
                                       Index Cond: (id = li.order_id)
                     ->  Index Scan using line_items_pkey on line_items li  (cost=0.00..8.43 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=269827)
                           Index Cond: (id = li.id)
               ->  Index Scan using orders_pkey on orders  (cost=0.00..7.56 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=269827)
                     Index Cond: (id = li.order_id)
         ->  Index Scan using products_pkey on products  (cost=0.00..7.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=269827)
               Index Cond: (id = li.product_id)
 Total runtime: 5918.038 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	29.675 ms	0.5 %
HashAggregate	1	369.871 ms	6.3 %
Index Scan	6	4696.336 ms	79.4 %
Nested Loop	6	806.505 ms	13.6 %
Seq Scan	1	11.178 ms	0.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
line_items	2	1728.190 ms	29.2 %
Index Scan	2	1728.190 ms	100.0 %
mc_order_searches	1	0.049 ms	0.0 %
Index Scan	1	0.049 ms	100.0 %
orders	2	2428.443 ms	41.1 %
Index Scan	2	2428.443 ms	100.0 %
products	2	550.832 ms	9.3 %
Index Scan	1	539.654 ms	98.0 %
Seq Scan	1	11.178 ms	2.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ztr

Colorize:

Columns visibility:

Per node type stats

Per table stats