Result: Ly3 : Get bSMS status and ignore duplicates joins

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.000	0.000	↓ 0.0			→ Sort (cost=967014.97..967015.65 rows=269 width=17) (actual time=.. rows= loops=) Sort Key: b.service_id, d.shortcode
0.000	0.000	↓ 0.0			→ HashAggregate (cost=967000.75..967004.12 rows=269 width=17) (actual time=.. rows= loops=)
0.000	0.000	↓ 0.0			→ Hash Join (cost=954766.02..965729.15 rows=127160 width=17) (actual time=.. rows= loops=) Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
0.000	0.000	↓ 0.0			→ GroupAggregate (cost=473887.69..477884.59 rows=133230 width=30) (actual time=.. rows= loops=) Filter: (count(*) = 1)
0.000	0.000	↓ 0.0			→ Sort (cost=473887.69..474220.77 rows=133230 width=30) (actual time=.. rows= loops=) Sort Key: d.out_ext_sid, d.status
0.000	0.000	↓ 0.0			→ Hash Join (cost=228285.21..459358.95 rows=133230 width=30) (actual time=.. rows= loops=) Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
0.000	0.000	↓ 0.0			→ Seq Scan on delivery_status d (cost=0.00..176048.64 rows=2491840 width=30) (actual time=.. rows= loops=) Filter: (status <> ALL ('{8,4}'::integer[]))
0.000	0.000	↓ 0.0			→ Hash (cost=225513.64..225513.64 rows=147006 width=25) (actual time=.. rows= loops=)
0.000	0.000	↓ 0.0			→ Bitmap Heap Scan on bsms_in b (cost=6451.56..225513.64 rows=147006 width=25) (actual time=.. rows= loops=) Recheck Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone) Filter: (operator_id = 1)
0.000	0.000	↓ 0.0			→ Bitmap Index Scan on new_bsms_in_otime_idx (cost=0.00..6414.80 rows=274949 width=0) (actual time=.. rows= loops=) Index Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)
0.000	0.000	↓ 0.0			→ Hash (cost=477650.95..477650.95 rows=133230 width=70) (actual time=.. rows= loops=)
0.000	0.000	↓ 0.0			→ Hash Join (cost=228428.21..477650.95 rows=133230 width=70) (actual time=.. rows= loops=) Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
0.000	0.000	↓ 0.0			→ Seq Scan on delivery_status d (cost=0.00..176048.64 rows=2491840 width=35) (actual time=.. rows= loops=) Filter: (status <> ALL ('{8,4}'::integer[]))
0.000	0.000	↓ 0.0			→ Hash (cost=225513.64..225513.64 rows=147006 width=35) (actual time=.. rows= loops=)
0.000	0.000	↓ 0.0			→ Bitmap Heap Scan on bsms_in b (cost=6451.56..225513.64 rows=147006 width=35) (actual time=.. rows= loops=) Recheck Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone) Filter: (operator_id = 1)
0.000	0.000	↓ 0.0			→ Bitmap Index Scan on new_bsms_in_otime_idx (cost=0.00..6414.80 rows=274949 width=0) (actual time=.. rows= loops=) Index Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)

                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=967014.97..967015.65 rows=269 width=17)
   Sort Key: b.service_id, d.shortcode
   ->  HashAggregate  (cost=967000.75..967004.12 rows=269 width=17)
         ->  Hash Join  (cost=954766.02..965729.15 rows=127160 width=17)
               Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
               ->  GroupAggregate  (cost=473887.69..477884.59 rows=133230 width=30)
                     Filter: (count(*) = 1)
                     ->  Sort  (cost=473887.69..474220.77 rows=133230 width=30)
                           Sort Key: d.out_ext_sid, d.status
                           ->  Hash Join  (cost=228285.21..459358.95 rows=133230 width=30)
                                 Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
                                 ->  Seq Scan on delivery_status d  (cost=0.00..176048.64 rows=2491840 width=30)
                                       Filter: (status <> ALL ('{8,4}'::integer[]))
                                 ->  Hash  (cost=225513.64..225513.64 rows=147006 width=25)
                                       ->  Bitmap Heap Scan on bsms_in b  (cost=6451.56..225513.64 rows=147006 width=25)
                                             Recheck Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)
                                             Filter: (operator_id = 1)
                                             ->  Bitmap Index Scan on new_bsms_in_otime_idx  (cost=0.00..6414.80 rows=274949 width=0)
                                                   Index Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)
               ->  Hash  (cost=477650.95..477650.95 rows=133230 width=70)
                     ->  Hash Join  (cost=228428.21..477650.95 rows=133230 width=70)
                           Hash Cond: ((d.out_ext_sid)::text = (b.ext_id)::text)
                           ->  Seq Scan on delivery_status d  (cost=0.00..176048.64 rows=2491840 width=35)
                                 Filter: (status <> ALL ('{8,4}'::integer[]))
                           ->  Hash  (cost=225513.64..225513.64 rows=147006 width=35)
                                 ->  Bitmap Heap Scan on bsms_in b  (cost=6451.56..225513.64 rows=147006 width=35)
                                       Recheck Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)
                                       Filter: (operator_id = 1)
                                       ->  Bitmap Index Scan on new_bsms_in_otime_idx  (cost=0.00..6414.80 rows=274949 width=0)
                                             Index Cond: (date_trunc('month'::text, otime) = '2012-01-01 00:00:00'::timestamp without time zone)
(30 rows)

Time: 3.193 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	0.000 ms	0.0 %
Bitmap Index Scan	2	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	3	0.000 ms	0.0 %
Hash Join	3	0.000 ms	0.0 %
HashAggregate	1	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
bsms_in	2	0.000 ms	0.0 %
Bitmap Heap Scan	2	0.000 ms	0.0 %
delivery_status	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Ly3 : Get bSMS status and ignore duplicates joins

Colorize:

Columns visibility:

Per node type stats

Per table stats