Result: dWas

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.003	229.151	↓ 2.5	10	1	→ Limit (cost=38787.96..38881.42 rows=4 width=98) (actual time=229.018..229.151 rows=10 loops=1)
0.004	229.148	↓ 2.5	10	1	→ Nested Loop (cost=38787.96..38881.42 rows=4 width=98) (actual time=229.016..229.148 rows=10 loops=1)
0.015	229.104	↓ 2.5	10	1	→ Nested Loop (cost=38787.96..38852.06 rows=4 width=65) (actual time=229.002..229.104 rows=10 loops=1)
0.006	229.039	↓ 2.5	10	1	→ Nested Loop (cost=38787.96..38821.79 rows=4 width=26) (actual time=228.991..229.039 rows=10 loops=1)
0.293	228.983	↓ 2.5	10	1	→ HashAggregate (cost=38787.96..38788.00 rows=4 width=4) (actual time=228.979..228.983 rows=10 loops=1)
12.390	228.690	↓ 213.0	852	1	→ Nested Loop (cost=0.00..38787.95 rows=4 width=4) (actual time=11.164..228.690 rows=852 loops=1) Join Filter: (((public.orders.created_on)::date >= mos.start_date) AND ((public.orders.created_on)::date < mos.end_date))
11.000	74.427	↓ 1278.1	47291	1	→ Nested Loop (cost=0.00..38507.52 rows=37 width=16) (actual time=0.110..74.427 rows=47291 loops=1) Join Filter: (COALESCE(mos.quantity, li.quantity) = li.quantity)
17.842	21.712	↓ 2.0	135	1	→ Nested Loop (cost=0.00..2243.93 rows=69 width=16) (actual time=0.091..21.712 rows=135 loops=1) Join Filter: ((public.products.name)::text ~~* (('%'::text \|\| mos.search_term) \|\| '%'::text))
0.008	0.008	↑ 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.007..0.008 rows=1 loops=1) Index Cond: (mc_order_search_id = 2)
3.862	3.862	↑ 1.0	13806	1	→ Seq Scan on products (cost=0.00..1994.06 rows=13806 width=41) (actual time=0.003..3.862 rows=13806 loops=1)
41.715	41.715	↓ 1.4	350	135	→ 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.011..0.309 rows=350 loops=135) Index Cond: (product_id = public.products.id) Filter: (((state)::text <> 'canceled'::text) AND ((state)::text <> 'canceled'::text))
141.873	141.873	↑ 1.0	1	47291	→ Index Scan using orders_pkey on orders (cost=0.00..7.56 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=47291) Index Cond: (id = li.order_id)
0.050	0.050	↑ 1.0	1	10	→ Index Scan using line_items_pkey on line_items li (cost=0.00..8.43 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=10) Index Cond: (id = li.id)
0.050	0.050	↑ 1.0	1	10	→ Index Scan using orders_pkey on orders (cost=0.00..7.56 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=10) Index Cond: (id = li.order_id)
0.040	0.040	↑ 1.0	1	10	→ Index Scan using products_pkey on products (cost=0.00..7.32 rows=1 width=41) (actual time=0.003..0.004 rows=1 loops=10) Index Cond: (id = li.product_id)

 Limit  (cost=38787.96..38881.42 rows=4 width=98) (actual time=229.018..229.151 rows=10 loops=1)
   ->  Nested Loop  (cost=38787.96..38881.42 rows=4 width=98) (actual time=229.016..229.148 rows=10 loops=1)
         ->  Nested Loop  (cost=38787.96..38852.06 rows=4 width=65) (actual time=229.002..229.104 rows=10 loops=1)
               ->  Nested Loop  (cost=38787.96..38821.79 rows=4 width=26) (actual time=228.991..229.039 rows=10 loops=1)
                     ->  HashAggregate  (cost=38787.96..38788.00 rows=4 width=4) (actual time=228.979..228.983 rows=10 loops=1)
                           ->  Nested Loop  (cost=0.00..38787.95 rows=4 width=4) (actual time=11.164..228.690 rows=852 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.110..74.427 rows=47291 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.091..21.712 rows=135 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.007..0.008 rows=1 loops=1)
                                                   Index Cond: (mc_order_search_id = 2)
                                             ->  Seq Scan on products  (cost=0.00..1994.06 rows=13806 width=41) (actual time=0.003..3.862 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.011..0.309 rows=350 loops=135)
                                             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.003..0.003 rows=1 loops=47291)
                                       Index Cond: (id = li.order_id)
                     ->  Index Scan using line_items_pkey on line_items li  (cost=0.00..8.43 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=10)
                           Index Cond: (id = li.id)
               ->  Index Scan using orders_pkey on orders  (cost=0.00..7.56 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=10)
                     Index Cond: (id = li.order_id)
         ->  Index Scan using products_pkey on products  (cost=0.00..7.32 rows=1 width=41) (actual time=0.003..0.004 rows=1 loops=10)
               Index Cond: (id = li.product_id)

Per node type stats

node type	count	sum of times	% of query
HashAggregate	1	0.293 ms	0.1 %
Index Scan	6	183.736 ms	80.2 %
Limit	1	0.003 ms	0.0 %
Nested Loop	6	41.257 ms	18.0 %
Seq Scan	1	3.862 ms	1.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
line_items	2	41.765 ms	18.2 %
Index Scan	2	41.765 ms	100.0 %
mc_order_searches	1	0.008 ms	0.0 %
Index Scan	1	0.008 ms	100.0 %
orders	2	141.923 ms	61.9 %
Index Scan	2	141.923 ms	100.0 %
products	2	3.902 ms	1.7 %
Index Scan	1	0.040 ms	1.0 %
Seq Scan	1	3.862 ms	99.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: dWas

Colorize:

Columns visibility:

Per node type stats

Per table stats