Result: blgO

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.004	4020.099	↑ 1.0	1	1	→ Limit (cost=9459.08..9459.09 rows=1 width=276) (actual time=4020.097..4020.099 rows=1 loops=1)
0.033	4020.095	↑ 1.0	1	1	→ Sort (cost=9459.08..9459.09 rows=1 width=276) (actual time=4020.094..4020.095 rows=1 loops=1) Sort Key: c.created, order_row.created Sort Method: quicksort Memory: 25kB
0.032	4020.062	↑ 1.0	1	1	→ Nested Loop Anti Join (cost=92.19..9459.07 rows=1 width=276) (actual time=3280.990..4020.062 rows=1 loops=1)
0.010	3988.538	↑ 1.0	1	1	→ Nested Loop (cost=92.19..9423.75 rows=1 width=276) (actual time=3249.467..3988.538 rows=1 loops=1)
0.006	3981.915	↑ 1.0	1	1	→ Nested Loop Left Join (cost=92.19..9415.47 rows=1 width=301) (actual time=3242.847..3981.915 rows=1 loops=1)
0.009	3968.449	↑ 1.0	1	1	→ Nested Loop Left Join (cost=92.19..9406.84 rows=1 width=286) (actual time=3229.383..3968.449 rows=1 loops=1)
0.007	3962.647	↑ 1.0	1	1	→ Nested Loop (cost=92.19..9399.31 rows=1 width=273) (actual time=3223.583..3962.647 rows=1 loops=1)
0.005	3960.606	↑ 1.0	1	1	→ Nested Loop Left Join (cost=92.19..9390.71 rows=1 width=273) (actual time=3221.544..3960.606 rows=1 loops=1)
0.007	3960.599	↑ 1.0	1	1	→ Nested Loop (cost=92.19..9382.09 rows=1 width=291) (actual time=3221.538..3960.599 rows=1 loops=1)
0.014	3949.468	↑ 1.0	1	1	→ Nested Loop Left Join (cost=92.19..9372.13 rows=1 width=258) (actual time=3210.409..3949.468 rows=1 loops=1) Filter: ((action."timestamp" < '2012-01-03 14:15:17.38586'::timestamp without time zone) OR (action."timestamp" IS NULL))
0.013	3940.181	↑ 1.0	1	1	→ Nested Loop (cost=92.19..9363.81 rows=1 width=240) (actual time=3201.125..3940.181 rows=1 loops=1)
0.204	3930.875	↑ 1.0	1	1	→ Nested Loop (cost=92.19..9353.86 rows=1 width=253) (actual time=3191.821..3930.875 rows=1 loops=1)
0.299	2898.395	↓ 84.0	84	1	→ Nested Loop (cost=92.19..9344.90 rows=1 width=212) (actual time=184.118..2898.395 rows=84 loops=1)
0.289	2868.024	↓ 84.0	84	1	→ Nested Loop (cost=92.19..9336.62 rows=1 width=235) (actual time=183.995..2868.024 rows=84 loops=1)
0.369	2767.775	↓ 84.0	84	1	→ Nested Loop (cost=92.19..9328.26 rows=1 width=221) (actual time=146.699..2767.775 rows=84 loops=1)
0.436	2597.978	↓ 84.0	84	1	→ Nested Loop (cost=92.19..9320.63 rows=1 width=161) (actual time=133.536..2597.978 rows=84 loops=1)
0.390	1716.214	↓ 84.0	84	1	→ Nested Loop (cost=92.19..9311.44 rows=1 width=164) (actual time=98.962..1716.214 rows=84 loops=1)
226.911	280.852	↓ 84.0	84	1	→ Bitmap Heap Scan on service (cost=92.19..9301.57 rows=1 width=98) (actual time=71.357..280.852 rows=84 loops=1) Recheck Cond: ((store_xid)::text = '54D1441D43A14DF9C32AD2787E0AB231'::text) Filter: (((xid)::text = (xid)::text) AND ((store_status)::text <> ALL ('{ready_in_store,closed,closed_loan_not_returned,cancelled}'::text[])) AND ((xid)::text = ((xid)::text \|\| ''::text)))
53.941	53.941	↑ 4.8	509	1	→ Bitmap Index Scan on service_2 (cost=0.00..92.19 rows=2448 width=0) (actual time=53.941..53.941 rows=509 loops=1) Index Cond: ((store_xid)::text = '54D1441D43A14DF9C32AD2787E0AB231'::text)
1434.972	1434.972	↑ 1.0	1	84	→ Index Scan using service_object_1 on service_object (cost=0.00..9.86 rows=1 width=66) (actual time=16.854..17.083 rows=1 loops=84) Index Cond: ((service_object.service_xid)::text = (service.xid)::text) Filter: ((service_object.service_object_type)::text = 'main'::text)
881.328	881.328	↑ 1.0	1	84	→ Index Scan using product_instance_pkey on article_instance (cost=0.00..9.18 rows=1 width=63) (actual time=10.491..10.492 rows=1 loops=84) Index Cond: ((article_instance.xid)::text = (service_object.xid)::text)
169.428	169.428	↑ 1.0	1	84	→ Index Scan using article_pkey on article a (cost=0.00..7.62 rows=1 width=90) (actual time=2.015..2.017 rows=1 loops=84) Index Cond: ((a.xid)::text = (article_instance.article_xid)::text)
99.960	99.960	↑ 1.0	1	84	→ Index Scan using organisation_pkey on organisation w (cost=0.00..8.34 rows=1 width=46) (actual time=1.189..1.190 rows=1 loops=84) Index Cond: ((w.xid)::text = (service.workshop_xid)::text)
30.072	30.072	↑ 1.0	1	84	→ Index Scan using debit_account_pkey on debit_account deb (cost=0.00..8.28 rows=1 width=43) (actual time=0.356..0.358 rows=1 loops=84) Index Cond: ((deb.xid)::text = (service.debit_account_xid)::text)
1032.276	1032.276	↓ 0.0	0	84	→ Index Scan using cost_1 on cost c (cost=0.00..8.94 rows=1 width=41) (actual time=12.285..12.289 rows=0 loops=84) Index Cond: ((c.order_row_xid)::text = (service.xid)::text) Filter: (((c.cost_type)::text = ANY ('{store,workshop}'::text[])) AND ((c.cost_status)::text = 'no_response'::text))
9.293	9.293	↑ 1.0	1	1	→ Index Scan using order_row_pkey on order_row (cost=0.00..9.94 rows=1 width=86) (actual time=9.292..9.293 rows=1 loops=1) Index Cond: ((order_row.xid)::text = (service.xid)::text)
9.273	9.273	↑ 1.0	1	1	→ Index Scan using order_row_action_pkey on order_row_action action (cost=0.00..8.31 rows=1 width=59) (actual time=9.272..9.273 rows=1 loops=1) Index Cond: (((action.order_row_xid)::text = (service.xid)::text) AND ((action.action_type)::text = 'estimate_contacted'::text))
11.124	11.124	↑ 1.0	1	1	→ Index Scan using order_data_pkey on order_data o (cost=0.00..9.94 rows=1 width=99) (actual time=11.123..11.124 rows=1 loops=1) Index Cond: ((o.xid)::text = (order_row.order_xid)::text)
0.002	0.002	↓ 0.0	0	1	→ Index Scan using person_pkey on person co (cost=0.00..8.61 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: ((o.contact_xid)::text = (co.xid)::text)
2.034	2.034	↑ 1.0	1	1	→ Index Scan using customer_pkey on customer cu (cost=0.00..8.58 rows=1 width=66) (actual time=2.032..2.034 rows=1 loops=1) Index Cond: ((cu.xid)::text = (o.customer_xid)::text)
5.793	5.793	↓ 0.0	0	1	→ Index Scan using organisation_pkey on organisation cu_o (cost=0.00..7.53 rows=1 width=46) (actual time=5.793..5.793 rows=0 loops=1) Index Cond: ((cu.entity_xid)::text = (cu_o.xid)::text)
13.460	13.460	↑ 1.0	1	1	→ Index Scan using person_pkey on person cu_p (cost=0.00..8.61 rows=1 width=48) (actual time=13.459..13.460 rows=1 loops=1) Index Cond: ((cu.entity_xid)::text = (cu_p.xid)::text)
6.613	6.613	↑ 1.0	1	1	→ Index Scan using brand_pkey on brand b (cost=0.00..8.27 rows=1 width=40) (actual time=6.612..6.613 rows=1 loops=1) Index Cond: ((b.xid)::text = (a.brand_xid)::text)
31.428	31.428	↓ 0.0	0	1	→ Index Scan using workshop_status_1 on workshop_status ws (cost=0.00..89.53 rows=7 width=33) (actual time=31.428..31.428 rows=0 loops=1) Index Cond: ((ws.order_row_xid)::text = (service.xid)::text) Filter: ((ws.status_type)::text = ANY ('{left_workshop,finished_delivery_note,finished,cancelled}'::text[]))
					SubPlan (forNested Loop Anti Join)
0.016	0.064	↑ 1.0	1	1	→ Aggregate (cost=12.60..12.61 rows=1 width=33) (actual time=0.064..0.064 rows=1 loops=1)
0.048	0.048	↓ 1.5	3	1	→ Index Scan using comment_1 on comment c (cost=0.00..12.60 rows=2 width=33) (actual time=0.046..0.048 rows=3 loops=1) Index Cond: ((order_row_xid)::text = ($0)::text) Filter: (NOT deleted)

                                                                                                                                              QUERY PLAN                                                                                                                       
                       
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
 Limit  (cost=9459.08..9459.09 rows=1 width=276) (actual time=4020.097..4020.099 rows=1 loops=1)
   ->  Sort  (cost=9459.08..9459.09 rows=1 width=276) (actual time=4020.094..4020.095 rows=1 loops=1)
         Sort Key: c.created, order_row.created
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop Anti Join  (cost=92.19..9459.07 rows=1 width=276) (actual time=3280.990..4020.062 rows=1 loops=1)
               ->  Nested Loop  (cost=92.19..9423.75 rows=1 width=276) (actual time=3249.467..3988.538 rows=1 loops=1)
                     ->  Nested Loop Left Join  (cost=92.19..9415.47 rows=1 width=301) (actual time=3242.847..3981.915 rows=1 loops=1)
                           ->  Nested Loop Left Join  (cost=92.19..9406.84 rows=1 width=286) (actual time=3229.383..3968.449 rows=1 loops=1)
                                 ->  Nested Loop  (cost=92.19..9399.31 rows=1 width=273) (actual time=3223.583..3962.647 rows=1 loops=1)
                                       ->  Nested Loop Left Join  (cost=92.19..9390.71 rows=1 width=273) (actual time=3221.544..3960.606 rows=1 loops=1)
                                             ->  Nested Loop  (cost=92.19..9382.09 rows=1 width=291) (actual time=3221.538..3960.599 rows=1 loops=1)
                                                   ->  Nested Loop Left Join  (cost=92.19..9372.13 rows=1 width=258) (actual time=3210.409..3949.468 rows=1 loops=1)
                                                         Filter: ((action."timestamp" < '2012-01-03 14:15:17.38586'::timestamp without time zone) OR (action."timestamp" IS NULL))
                                                         ->  Nested Loop  (cost=92.19..9363.81 rows=1 width=240) (actual time=3201.125..3940.181 rows=1 loops=1)
                                                               ->  Nested Loop  (cost=92.19..9353.86 rows=1 width=253) (actual time=3191.821..3930.875 rows=1 loops=1)
                                                                     ->  Nested Loop  (cost=92.19..9344.90 rows=1 width=212) (actual time=184.118..2898.395 rows=84 loops=1)
                                                                           ->  Nested Loop  (cost=92.19..9336.62 rows=1 width=235) (actual time=183.995..2868.024 rows=84 loops=1)
                                                                                 ->  Nested Loop  (cost=92.19..9328.26 rows=1 width=221) (actual time=146.699..2767.775 rows=84 loops=1)
                                                                                       ->  Nested Loop  (cost=92.19..9320.63 rows=1 width=161) (actual time=133.536..2597.978 rows=84 loops=1)
                                                                                             ->  Nested Loop  (cost=92.19..9311.44 rows=1 width=164) (actual time=98.962..1716.214 rows=84 loops=1)
                                                                                                   ->  Bitmap Heap Scan on service  (cost=92.19..9301.57 rows=1 width=98) (actual time=71.357..280.852 rows=84 loops=1)
                                                                                                         Recheck Cond: ((store_xid)::text = '54D1441D43A14DF9C32AD2787E0AB231'::text)
                                                                                                         Filter: (((xid)::text = (xid)::text) AND ((store_status)::text <> ALL ('{ready_in_store,closed,closed_loan_not_returned,cancelled}'::text[])) AND ((xid)::text = ((xid)::text || ''::text)))
                                                                                                         ->  Bitmap Index Scan on service_2  (cost=0.00..92.19 rows=2448 width=0) (actual time=53.941..53.941 rows=509 loops=1)
                                                                                                               Index Cond: ((store_xid)::text = '54D1441D43A14DF9C32AD2787E0AB231'::text)
                                                                                                   ->  Index Scan using service_object_1 on service_object  (cost=0.00..9.86 rows=1 width=66) (actual time=16.854..17.083 rows=1 loops=84)
                                                                                                         Index Cond: ((service_object.service_xid)::text = (service.xid)::text)
                                                                                                         Filter: ((service_object.service_object_type)::text = 'main'::text)
                                                                                             ->  Index Scan using product_instance_pkey on article_instance  (cost=0.00..9.18 rows=1 width=63) (actual time=10.491..10.492 rows=1 loops=84)
                                                                                                   Index Cond: ((article_instance.xid)::text = (service_object.xid)::text)
                                                                                       ->  Index Scan using article_pkey on article a  (cost=0.00..7.62 rows=1 width=90) (actual time=2.015..2.017 rows=1 loops=84)
                                                                                             Index Cond: ((a.xid)::text = (article_instance.article_xid)::text)
                                                                                 ->  Index Scan using organisation_pkey on organisation w  (cost=0.00..8.34 rows=1 width=46) (actual time=1.189..1.190 rows=1 loops=84)
                                                                                       Index Cond: ((w.xid)::text = (service.workshop_xid)::text)
                                                                           ->  Index Scan using debit_account_pkey on debit_account deb  (cost=0.00..8.28 rows=1 width=43) (actual time=0.356..0.358 rows=1 loops=84)
                                                                                 Index Cond: ((deb.xid)::text = (service.debit_account_xid)::text)
                                                                     ->  Index Scan using cost_1 on cost c  (cost=0.00..8.94 rows=1 width=41) (actual time=12.285..12.289 rows=0 loops=84)
                                                                           Index Cond: ((c.order_row_xid)::text = (service.xid)::text)
                                                                           Filter: (((c.cost_type)::text = ANY ('{store,workshop}'::text[])) AND ((c.cost_status)::text = 'no_response'::text))
                                                               ->  Index Scan using order_row_pkey on order_row  (cost=0.00..9.94 rows=1 width=86) (actual time=9.292..9.293 rows=1 loops=1)
                                                                     Index Cond: ((order_row.xid)::text = (service.xid)::text)
                                                         ->  Index Scan using order_row_action_pkey on order_row_action action  (cost=0.00..8.31 rows=1 width=59) (actual time=9.272..9.273 rows=1 loops=1)
                                                               Index Cond: (((action.order_row_xid)::text = (service.xid)::text) AND ((action.action_type)::text = 'estimate_contacted'::text))
                                                   ->  Index Scan using order_data_pkey on order_data o  (cost=0.00..9.94 rows=1 width=99) (actual time=11.123..11.124 rows=1 loops=1)
                                                         Index Cond: ((o.xid)::text = (order_row.order_xid)::text)
                                             ->  Index Scan using person_pkey on person co  (cost=0.00..8.61 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=1)
                                                   Index Cond: ((o.contact_xid)::text = (co.xid)::text)
                                       ->  Index Scan using customer_pkey on customer cu  (cost=0.00..8.58 rows=1 width=66) (actual time=2.032..2.034 rows=1 loops=1)
                                             Index Cond: ((cu.xid)::text = (o.customer_xid)::text)
                                 ->  Index Scan using organisation_pkey on organisation cu_o  (cost=0.00..7.53 rows=1 width=46) (actual time=5.793..5.793 rows=0 loops=1)
                                       Index Cond: ((cu.entity_xid)::text = (cu_o.xid)::text)
                           ->  Index Scan using person_pkey on person cu_p  (cost=0.00..8.61 rows=1 width=48) (actual time=13.459..13.460 rows=1 loops=1)
                                 Index Cond: ((cu.entity_xid)::text = (cu_p.xid)::text)
                     ->  Index Scan using brand_pkey on brand b  (cost=0.00..8.27 rows=1 width=40) (actual time=6.612..6.613 rows=1 loops=1)
                           Index Cond: ((b.xid)::text = (a.brand_xid)::text)
               ->  Index Scan using workshop_status_1 on workshop_status ws  (cost=0.00..89.53 rows=7 width=33) (actual time=31.428..31.428 rows=0 loops=1)
                     Index Cond: ((ws.order_row_xid)::text = (service.xid)::text)
                     Filter: ((ws.status_type)::text = ANY ('{left_workshop,finished_delivery_note,finished,cancelled}'::text[]))

               SubPlan 1
                 ->  Aggregate  (cost=12.60..12.61 rows=1 width=33) (actual time=0.064..0.064 rows=1 loops=1)
                       ->  Index Scan using comment_1 on comment c  (cost=0.00..12.60 rows=2 width=33) (actual time=0.046..0.048 rows=3 loops=1)
                             Index Cond: ((order_row_xid)::text = ($0)::text)
                             Filter: (NOT deleted)
 Total runtime: 4020.687 ms
(64 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.016 ms	0.0 %
Bitmap Heap Scan	1	226.911 ms	5.6 %
Bitmap Index Scan	1	53.941 ms	1.3 %
Index Scan	16	3737.104 ms	93.0 %
Limit	1	0.004 ms	0.0 %
Nested Loop	10	2.024 ms	0.1 %
Nested Loop Anti Join	1	0.032 ms	0.0 %
Nested Loop Left Join	4	0.034 ms	0.0 %
Sort	1	0.033 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
article	1	169.428 ms	4.2 %
Index Scan	1	169.428 ms	100.0 %
article_instance	1	881.328 ms	21.9 %
Index Scan	1	881.328 ms	100.0 %
brand	1	6.613 ms	0.2 %
Index Scan	1	6.613 ms	100.0 %
comment	1	0.048 ms	0.0 %
Index Scan	1	0.048 ms	100.0 %
cost	1	1032.276 ms	25.7 %
Index Scan	1	1032.276 ms	100.0 %
customer	1	2.034 ms	0.1 %
Index Scan	1	2.034 ms	100.0 %
debit_account	1	30.072 ms	0.7 %
Index Scan	1	30.072 ms	100.0 %
order_data	1	11.124 ms	0.3 %
Index Scan	1	11.124 ms	100.0 %
order_row	1	9.293 ms	0.2 %
Index Scan	1	9.293 ms	100.0 %
order_row_action	1	9.273 ms	0.2 %
Index Scan	1	9.273 ms	100.0 %
organisation	2	105.753 ms	2.6 %
Index Scan	2	105.753 ms	100.0 %
person	2	13.462 ms	0.3 %
Index Scan	2	13.462 ms	100.0 %
service	1	226.911 ms	5.6 %
Bitmap Heap Scan	1	226.911 ms	100.0 %
service_object	1	1434.972 ms	35.7 %
Index Scan	1	1434.972 ms	100.0 %
workshop_status	1	31.428 ms	0.8 %
Index Scan	1	31.428 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: blgO

Colorize:

Columns visibility:

Per node type stats

Per table stats