Result: qNo : remove IN in LOOP query

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.441	3541.663	↓ 114.0	228	1	→ Group (cost=79928.66..79928.77 rows=2 width=1676) (actual time=3541.097..3541.663 rows=228 loops=1)
0.681	3541.222	↓ 117.5	235	1	→ Sort (cost=79928.66..79928.67 rows=2 width=1676) (actual time=3541.094..3541.222 rows=235 loops=1) Sort Key: co.placeddate, co.ordernumber, co.orderid, p.firstname, p.lastname, co.customerpin, co.shippingtype, co.trackingnumber, co.filleddate, co.problemorder, co.orderhistory, st.statusid, st.title, l.transid, ex.neworderid, rqa.i_st_add_country, (CASE WHEN (ex.neworderid IS NULL) THEN false ELSE true END), rsa.result, ps.postalservicename, shiptype.shipmentdescription Sort Method: quicksort Memory: 114kB
1.392	3540.541	↓ 117.5	235	1	→ Hash Left Join (cost=79921.74..79928.65 rows=2 width=1676) (actual time=3535.304..3540.541 rows=235 loops=1) Hash Cond: (((rqa.request_authorize_id)::character varying)::text = (rsa.request_authorize_id)::text) Filter: (((rsa.result)::text = ANY ('{0,126,127}'::text[])) OR (CASE WHEN (ex.neworderid IS NULL) THEN false ELSE true END))
0.518	2866.801	↓ 145.5	291	1	→ Hash Left Join (cost=31725.25..31732.09 rows=2 width=1678) (actual time=2862.560..2866.801 rows=291 loops=1) Hash Cond: (co.orderid = exchange.neworderid)
0.891	2862.166	↓ 145.5	291	1	→ Nested Loop Left Join (cost=31626.59..31633.40 rows=2 width=1678) (actual time=2858.412..2862.166 rows=291 loops=1)
0.936	2858.491	↓ 232.0	232	1	→ Sort (cost=31626.59..31626.60 rows=1 width=313) (actual time=2858.360..2858.491 rows=232 loops=1) Sort Key: co.dateadded Sort Method: quicksort Memory: 113kB
406.495	2857.555	↓ 232.0	232	1	→ Nested Loop Left Join (cost=28506.02..31626.58 rows=1 width=313) (actual time=105.324..2857.555 rows=232 loops=1) Join Filter: (ex.neworderid = co.orderid)
918.973	2053.412	↓ 232.0	232	1	→ Nested Loop (cost=28506.02..31527.92 rows=1 width=305) (actual time=101.716..2053.412 rows=232 loops=1) Join Filter: (cos.orderid = cp.orderid)
1.060	117.337	↓ 283.0	283	1	→ Nested Loop (cost=28506.02..31353.69 rows=1 width=313) (actual time=39.135..117.337 rows=283 loops=1)
4.663	115.428	↓ 283.0	283	1	→ Nested Loop (cost=28506.02..31352.63 rows=1 width=312) (actual time=39.113..115.428 rows=283 loops=1) Join Filter: ((cop.peopleid)::text = (p.peopleid)::text)
1.870	97.829	↓ 196.0	588	1	→ Nested Loop (cost=28506.02..31350.55 rows=3 width=336) (actual time=31.604..97.829 rows=588 loops=1)
0.759	90.449	↓ 290.0	290	1	→ Nested Loop Left Join (cost=28506.01..31349.99 rows=1 width=299) (actual time=25.024..90.449 rows=290 loops=1)
0.955	89.110	↓ 290.0	290	1	→ Nested Loop (cost=28506.01..31349.70 rows=1 width=291) (actual time=25.001..89.110 rows=290 loops=1)
22.375	86.995	↓ 290.0	290	1	→ Hash Join (cost=28506.01..31349.42 rows=1 width=280) (actual time=24.967..86.995 rows=290 loops=1) Hash Cond: (cos.orderid = co.orderid)
41.532	41.532	↓ 1.1	36025	1	→ Seq Scan on customerordershipmentcarrier cos (cost=0.00..2716.60 rows=33813 width=12) (actual time=0.033..41.532 rows=36025 loops=1) Filter: (postalserviceid = 2)
2.080	23.088	↓ 1.1	1651	1	→ Hash (cost=28487.20..28487.20 rows=1505 width=268) (actual time=23.088..23.088 rows=1651 loops=1)
3.425	21.008	↓ 1.1	1651	1	→ Hash Join (cost=1.16..28487.20 rows=1505 width=268) (actual time=0.077..21.008 rows=1651 loops=1) Hash Cond: ((co.shippingtype)::text = ((shiptype.shipmentid)::character varying)::text)
17.553	17.553	↑ 156.2	1660	1	→ Index Scan using foo_idx on customerorder co (cost=0.00..26849.94 rows=259368 width=253) (actual time=0.038..17.553 rows=1660 loops=1) Filter: ((shippingtype)::text <> '20'::text)
0.013	0.030	↑ 1.0	7	1	→ Hash (cost=1.07..1.07 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1)
0.017	0.017	↑ 1.0	7	1	→ Seq Scan on shipmenttype shiptype (cost=0.00..1.07 rows=7 width=19) (actual time=0.013..0.017 rows=7 loops=1)
1.160	1.160	↑ 1.0	1	290	→ Index Scan using pk_statusid on statustype st (cost=0.00..0.27 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=290) Index Cond: (st.statusid = co.statusid)
0.580	0.580	↓ 0.0	0	290	→ Index Scan using index_linksharetransaction_orderid on linksharetransaction l (cost=0.00..0.27 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=290) Index Cond: (l.orderid = co.orderid)
5.510	5.510	↑ 1.0	2	290	→ Index Scan using index_customerorderpeople_orderid on customerorderpeople cop (cost=0.00..0.52 rows=2 width=45) (actual time=0.017..0.019 rows=2 loops=290) Index Cond: ((cop.orderid)::text = ((co.orderid)::character varying)::text)
12.936	12.936	↓ 1.5	3	588	→ Index Scan using index_people_uniquepin on people p (cost=0.00..0.66 rows=2 width=35) (actual time=0.011..0.022 rows=3 loops=588) Index Cond: ((p.uniquepin)::text = (co.customerpin)::text) Filter: (p.primarycontactflag = 'T'::bpchar)
0.849	0.849	↑ 1.0	1	283	→ Seq Scan on postalservice ps (cost=0.00..1.05 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=283) Filter: (ps.postalserviceid = 2)
1017.102	1017.102	↓ 1.0	5867	283	→ Seq Scan on customer_preorder cp (cost=0.00..102.83 rows=5712 width=8) (actual time=0.004..3.594 rows=5867 loops=283) Filter: cp.preorderstatus
397.648	397.648	↓ 1.0	3042	232	→ Seq Scan on exchange ex (cost=0.00..61.07 rows=3007 width=8) (actual time=0.003..1.714 rows=3042 loops=232)
2.784	2.784	↑ 2.0	1	232	→ Index Scan using index_orderid on request_authorize rqa (cost=0.00..6.77 rows=2 width=14) (actual time=0.010..0.012 rows=1 loops=232) Index Cond: (co.orderid = rqa.orderid)
2.140	4.117	↓ 1.0	3042	1	→ Hash (cost=61.07..61.07 rows=3007 width=8) (actual time=4.117..4.117 rows=3042 loops=1)
1.977	1.977	↓ 1.0	3042	1	→ Seq Scan on exchange (cost=0.00..61.07 rows=3007 width=8) (actual time=0.005..1.977 rows=3042 loops=1)
265.519	672.348	↑ 1.0	324688	1	→ Hash (cost=44073.44..44073.44 rows=329844 width=8) (actual time=672.348..672.348 rows=324688 loops=1)
406.829	406.829	↑ 1.0	324688	1	→ Seq Scan on response_authorize rsa (cost=0.00..44073.44 rows=329844 width=8) (actual time=0.017..406.829 rows=324688 loops=1)

                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Group  (cost=79928.66..79928.77 rows=2 width=1676) (actual time=3541.097..3541.663 rows=228 loops=1)
   ->  Sort  (cost=79928.66..79928.67 rows=2 width=1676) (actual time=3541.094..3541.222 rows=235 loops=1)
         Sort Key: co.placeddate, co.ordernumber, co.orderid, p.firstname, p.lastname, co.customerpin, co.shippingtype, co.trackingnumber, co.filleddate, co.problemorder, co.orderhistory, st.statusid, st.title, l.transid, ex.neworderid, rqa.i_st_add_country, (CASE WHEN (ex.neworderid IS NULL) THEN false ELSE true END), rsa.result, ps.postalservicename, shiptype.shipmentdescription
         Sort Method:  quicksort  Memory: 114kB
         ->  Hash Left Join  (cost=79921.74..79928.65 rows=2 width=1676) (actual time=3535.304..3540.541 rows=235 loops=1)
               Hash Cond: (((rqa.request_authorize_id)::character varying)::text = (rsa.request_authorize_id)::text)
               Filter: (((rsa.result)::text = ANY ('{0,126,127}'::text[])) OR (CASE WHEN (ex.neworderid IS NULL) THEN false ELSE true END))
               ->  Hash Left Join  (cost=31725.25..31732.09 rows=2 width=1678) (actual time=2862.560..2866.801 rows=291 loops=1)
                     Hash Cond: (co.orderid = exchange.neworderid)
                     ->  Nested Loop Left Join  (cost=31626.59..31633.40 rows=2 width=1678) (actual time=2858.412..2862.166 rows=291 loops=1)
                           ->  Sort  (cost=31626.59..31626.60 rows=1 width=313) (actual time=2858.360..2858.491 rows=232 loops=1)
                                 Sort Key: co.dateadded
                                 Sort Method:  quicksort  Memory: 113kB
                                 ->  Nested Loop Left Join  (cost=28506.02..31626.58 rows=1 width=313) (actual time=105.324..2857.555 rows=232 loops=1)
                                       Join Filter: (ex.neworderid = co.orderid)
                                       ->  Nested Loop  (cost=28506.02..31527.92 rows=1 width=305) (actual time=101.716..2053.412 rows=232 loops=1)
                                             Join Filter: (cos.orderid = cp.orderid)
                                             ->  Nested Loop  (cost=28506.02..31353.69 rows=1 width=313) (actual time=39.135..117.337 rows=283 loops=1)
                                                   ->  Nested Loop  (cost=28506.02..31352.63 rows=1 width=312) (actual time=39.113..115.428 rows=283 loops=1)
                                                         Join Filter: ((cop.peopleid)::text = (p.peopleid)::text)
                                                         ->  Nested Loop  (cost=28506.02..31350.55 rows=3 width=336) (actual time=31.604..97.829 rows=588 loops=1)
                                                               ->  Nested Loop Left Join  (cost=28506.01..31349.99 rows=1 width=299) (actual time=25.024..90.449 rows=290 loops=1)
                                                                     ->  Nested Loop  (cost=28506.01..31349.70 rows=1 width=291) (actual time=25.001..89.110 rows=290 loops=1)
                                                                           ->  Hash Join  (cost=28506.01..31349.42 rows=1 width=280) (actual time=24.967..86.995 rows=290 loops=1)
                                                                                 Hash Cond: (cos.orderid = co.orderid)
                                                                                 ->  Seq Scan on customerordershipmentcarrier cos  (cost=0.00..2716.60 rows=33813 width=12) (actual time=0.033..41.532 rows=36025 loops=1)
                                                                                       Filter: (postalserviceid = 2)
                                                                                 ->  Hash  (cost=28487.20..28487.20 rows=1505 width=268) (actual time=23.088..23.088 rows=1651 loops=1)
                                                                                       ->  Hash Join  (cost=1.16..28487.20 rows=1505 width=268) (actual time=0.077..21.008 rows=1651 loops=1)
                                                                                             Hash Cond: ((co.shippingtype)::text = ((shiptype.shipmentid)::character varying)::text)
                                                                                             ->  Index Scan using foo_idx on customerorder co  (cost=0.00..26849.94 rows=259368 width=253) (actual time=0.038..17.553 rows=1660 loops=1)
                                                                                                   Filter: ((shippingtype)::text <> '20'::text)
                                                                                             ->  Hash  (cost=1.07..1.07 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1)
                                                                                                   ->  Seq Scan on shipmenttype shiptype  (cost=0.00..1.07 rows=7 width=19) (actual time=0.013..0.017 rows=7 loops=1)
                                                                           ->  Index Scan using pk_statusid on statustype st  (cost=0.00..0.27 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=290)
                                                                                 Index Cond: (st.statusid = co.statusid)
                                                                     ->  Index Scan using index_linksharetransaction_orderid on linksharetransaction l  (cost=0.00..0.27 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=290)
                                                                           Index Cond: (l.orderid = co.orderid)
                                                               ->  Index Scan using index_customerorderpeople_orderid on customerorderpeople cop  (cost=0.00..0.52 rows=2 width=45) (actual time=0.017..0.019 rows=2 loops=290)
                                                                     Index Cond: ((cop.orderid)::text = ((co.orderid)::character varying)::text)
                                                         ->  Index Scan using index_people_uniquepin on people p  (cost=0.00..0.66 rows=2 width=35) (actual time=0.011..0.022 rows=3 loops=588)
                                                               Index Cond: ((p.uniquepin)::text = (co.customerpin)::text)
                                                               Filter: (p.primarycontactflag = 'T'::bpchar)
                                                   ->  Seq Scan on postalservice ps  (cost=0.00..1.05 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=283)
                                                         Filter: (ps.postalserviceid = 2)
                                             ->  Seq Scan on customer_preorder cp  (cost=0.00..102.83 rows=5712 width=8) (actual time=0.004..3.594 rows=5867 loops=283)
                                                   Filter: cp.preorderstatus
                                       ->  Seq Scan on exchange ex  (cost=0.00..61.07 rows=3007 width=8) (actual time=0.003..1.714 rows=3042 loops=232)
                           ->  Index Scan using index_orderid on request_authorize rqa  (cost=0.00..6.77 rows=2 width=14) (actual time=0.010..0.012 rows=1 loops=232)
                                 Index Cond: (co.orderid = rqa.orderid)
                     ->  Hash  (cost=61.07..61.07 rows=3007 width=8) (actual time=4.117..4.117 rows=3042 loops=1)
                           ->  Seq Scan on exchange  (cost=0.00..61.07 rows=3007 width=8) (actual time=0.005..1.977 rows=3042 loops=1)
               ->  Hash  (cost=44073.44..44073.44 rows=329844 width=8) (actual time=672.348..672.348 rows=324688 loops=1)
                     ->  Seq Scan on response_authorize rsa  (cost=0.00..44073.44 rows=329844 width=8) (actual time=0.017..406.829 rows=324688 loops=1)
 Total runtime: 3542.011 ms
(55 rows)

Per node type stats

node type	count	sum of times	% of query
Group	1	0.441 ms	0.0 %
Hash	4	269.752 ms	7.6 %
Hash Join	2	25.800 ms	0.7 %
Hash Left Join	2	1.910 ms	0.1 %
Index Scan	6	40.523 ms	1.1 %
Nested Loop	5	927.521 ms	26.2 %
Nested Loop Left Join	3	408.145 ms	11.5 %
Seq Scan	7	1865.954 ms	52.7 %
Sort	2	1.617 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
customer_preorder	1	1017.102 ms	28.7 %
Seq Scan	1	1017.102 ms	100.0 %
customerorder	1	17.553 ms	0.5 %
Index Scan	1	17.553 ms	100.0 %
customerorderpeople	1	5.510 ms	0.2 %
Index Scan	1	5.510 ms	100.0 %
customerordershipmentcarrier	1	41.532 ms	1.2 %
Seq Scan	1	41.532 ms	100.0 %
exchange	2	399.625 ms	11.3 %
Seq Scan	2	399.625 ms	100.0 %
linksharetransaction	1	0.580 ms	0.0 %
Index Scan	1	0.580 ms	100.0 %
people	1	12.936 ms	0.4 %
Index Scan	1	12.936 ms	100.0 %
postalservice	1	0.849 ms	0.0 %
Seq Scan	1	0.849 ms	100.0 %
request_authorize	1	2.784 ms	0.1 %
Index Scan	1	2.784 ms	100.0 %
response_authorize	1	406.829 ms	11.5 %
Seq Scan	1	406.829 ms	100.0 %
shipmenttype	1	0.017 ms	0.0 %
Seq Scan	1	0.017 ms	100.0 %
statustype	1	1.160 ms	0.0 %
Index Scan	1	1.160 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: qNo : remove IN in LOOP query

Colorize:

Columns visibility:

Per node type stats

Per table stats