#	exclusive	inclusive	rows x	rows	loops	node
1.	29.809	428406.158	↑ 158.2	13665	1	→ Unique (cost=2760831.89..3009517.21 rows=2162481 width=273) (actual time=428369.216..428406.158 rows=13665 loops=1)
2.	295.986	428376.349	↑ 158.2	13665	1	→ Sort (cost=2760831.89..2766238.09 rows=2162481 width=273) (actual time=428369.210..428376.349 rows=13665 loops=1) Sort Key: a.unioncd, b.unionnm, c.soccd, c.socnm, d.tcd, d.tnm, count(DISTINCT a.gracd), sum(a.gmlknpg_b), sum(a.gmlkpg_b), sum(a.dnpg_b), sum(a.dpg_b), sum(a.necnpg_b), sum(a.necpg_b), sum(a.beloneyr_b), sum(a.belotfnpg_b), sum(a.belotfpg_b), sum(a.totmlk_b), sum(a.gmlknpg_c), sum(a.gmlkpg_c), sum(a.dnpg_c), sum(a.dpg_c), sum(a.necnpg_c), sum(a.necpg_c), sum(a.beloneyr_c), sum(a.belotfnpg_c), sum(a.belotfpg_c), sum(a.totmlk_c), sum(a.catfeed), sum(a.minrmx), sum(a.gmlknpg_crb), sum(a.gmlkpg_crb), sum(a.dnpg_crb), sum(a.dpg_crb), sum(a.necnpg_crb), sum(a.necpg_crb), sum(a.beloneyr_crb), sum(a.belotfnpg_crb), sum(a.belotfpg_crb), sum(a.totmlk_crb), sum(a.socmlk_b), sum(a.primlk_b), sum(a.hsmlk_b), sum(a.socmlk_c), sum(a.primlk_c), sum(a.hsmlk_c)
3.	13558.839	428080.363	↑ 158.2	13665	1	→ GroupAggregate (cost=1252650.85..1733802.87 rows=2162481 width=273) (actual time=411572.658..428080.363 rows=13665 loops=1)
4.	399090.701	414521.524	↓ 1.0	2173334	1	→ Sort (cost=1252650.85..1258057.05 rows=2162481 width=273) (actual time=411571.103..414521.524 rows=2173334 loops=1) Sort Key: a.unioncd, b.unionnm, c.soccd, c.socnm, d.tcd, d.tnm
5.	3886.258	15430.823	↓ 1.0	2173334	1	→ Hash Join (cost=882.75..225621.83 rows=2162481 width=273) (actual time=199.971..15430.823 rows=2173334 loops=1) Hash Cond: (("outer".unioncd)::text = ("inner".unioncd)::text)
6.	6105.477	11544.519	↓ 1.0	2173334	1	→ Hash Join (cost=881.53..193183.40 rows=2162481 width=251) (actual time=199.826..11544.519 rows=2173334 loops=1) Hash Cond: (("outer".socid)::text = ("inner".socid)::text)
7.	5239.275	5239.275	↓ 1.0	2193320	1	→ Seq Scan on sms_hh_mst a (cost=0.00..126813.02 rows=2193202 width=220) (actual time=0.034..5239.275 rows=2193320 loops=1)
8.	17.310	199.767	↑ 1.0	15043	1	→ Hash (cost=843.70..843.70 rows=15134 width=55) (actual time=199.767..199.767 rows=15043 loops=1)
9.	22.347	182.457	↑ 1.0	15043	1	→ Hash Join (cost=5.83..843.70 rows=15134 width=55) (actual time=107.989..182.457 rows=15043 loops=1) Hash Cond: (("outer".talcd)::text = ("inner".talucaid)::text)
10.	52.147	52.147	↑ 1.0	15084	1	→ Seq Scan on sms_m_soc c (cost=0.00..610.86 rows=15134 width=47) (actual time=0.004..52.147 rows=15084 loops=1) Filter: ((vcd)::text <> '-1'::text)
11.	0.253	107.963	↑ 1.0	226	1	→ Hash (cost=5.26..5.26 rows=226 width=31) (actual time=107.963..107.963 rows=226 loops=1)
12.	107.710	107.710	↑ 1.0	226	1	→ Seq Scan on sms_taluka_mst d (cost=0.00..5.26 rows=226 width=31) (actual time=89.897..107.710 rows=226 loops=1)
13.	0.026	0.046	↑ 1.0	17	1	→ Hash (cost=1.17..1.17 rows=17 width=30) (actual time=0.046..0.046 rows=17 loops=1)
14.	0.020	0.020	↑ 1.0	17	1	→ Seq Scan on sms_union_mst b (cost=0.00..1.17 rows=17 width=30) (actual time=0.004..0.020 rows=17 loops=1)

Unique  (cost=2760831.89..3009517.21 rows=2162481 width=273) (actual time=428369.216..428406.158 rows=13665 loops=1)
  ->  Sort  (cost=2760831.89..2766238.09 rows=2162481 width=273) (actual time=428369.210..428376.349 rows=13665 loops=1)
        Sort Key: a.unioncd, b.unionnm, c.soccd, c.socnm, d.tcd, d.tnm, count(DISTINCT a.gracd), sum(a.gmlknpg_b), sum(a.gmlkpg_b), sum(a.dnpg_b), sum(a.dpg_b), sum(a.necnpg_b), sum(a.necpg_b), sum(a.beloneyr_b), sum(a.belotfnpg_b), sum(a.belotfpg_b), sum(a.totmlk_b), sum(a.gmlknpg_c), sum(a.gmlkpg_c), sum(a.dnpg_c), sum(a.dpg_c), sum(a.necnpg_c), sum(a.necpg_c), sum(a.beloneyr_c), sum(a.belotfnpg_c), sum(a.belotfpg_c), sum(a.totmlk_c), sum(a.catfeed), sum(a.minrmx), sum(a.gmlknpg_crb), sum(a.gmlkpg_crb), sum(a.dnpg_crb), sum(a.dpg_crb), sum(a.necnpg_crb), sum(a.necpg_crb), sum(a.beloneyr_crb), sum(a.belotfnpg_crb), sum(a.belotfpg_crb), sum(a.totmlk_crb), sum(a.socmlk_b), sum(a.primlk_b), sum(a.hsmlk_b), sum(a.socmlk_c), sum(a.primlk_c), sum(a.hsmlk_c)
        ->  GroupAggregate  (cost=1252650.85..1733802.87 rows=2162481 width=273) (actual time=411572.658..428080.363 rows=13665 loops=1)
              ->  Sort  (cost=1252650.85..1258057.05 rows=2162481 width=273) (actual time=411571.103..414521.524 rows=2173334 loops=1)
                    Sort Key: a.unioncd, b.unionnm, c.soccd, c.socnm, d.tcd, d.tnm
                    ->  Hash Join  (cost=882.75..225621.83 rows=2162481 width=273) (actual time=199.971..15430.823 rows=2173334 loops=1)
                          Hash Cond: (("outer".unioncd)::text = ("inner".unioncd)::text)
                          ->  Hash Join  (cost=881.53..193183.40 rows=2162481 width=251) (actual time=199.826..11544.519 rows=2173334 loops=1)
                                Hash Cond: (("outer".socid)::text = ("inner".socid)::text)
                                ->  Seq Scan on sms_hh_mst a  (cost=0.00..126813.02 rows=2193202 width=220) (actual time=0.034..5239.275 rows=2193320 loops=1)
                                ->  Hash  (cost=843.70..843.70 rows=15134 width=55) (actual time=199.767..199.767 rows=15043 loops=1)
                                      ->  Hash Join  (cost=5.83..843.70 rows=15134 width=55) (actual time=107.989..182.457 rows=15043 loops=1)
                                            Hash Cond: (("outer".talcd)::text = ("inner".talucaid)::text)
                                            ->  Seq Scan on sms_m_soc c  (cost=0.00..610.86 rows=15134 width=47) (actual time=0.004..52.147 rows=15084 loops=1)
                                                  Filter: ((vcd)::text <> '-1'::text)
                                            ->  Hash  (cost=5.26..5.26 rows=226 width=31) (actual time=107.963..107.963 rows=226 loops=1)
                                                  ->  Seq Scan on sms_taluka_mst d  (cost=0.00..5.26 rows=226 width=31) (actual time=89.897..107.710 rows=226 loops=1)
                          ->  Hash  (cost=1.17..1.17 rows=17 width=30) (actual time=0.046..0.046 rows=17 loops=1)
                                ->  Seq Scan on sms_union_mst b  (cost=0.00..1.17 rows=17 width=30) (actual time=0.004..0.020 rows=17 loops=1)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	13558.839 ms	3.2 %
Hash	3	17.589 ms	0.0 %
Hash Join	3	10014.082 ms	2.3 %
Seq Scan	4	5399.152 ms	1.3 %
Sort	2	399386.687 ms	93.2 %
Unique	1	29.809 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
sms_hh_mst	1	5239.275 ms	1.2 %
Seq Scan	1	5239.275 ms	100.0 %
sms_m_soc	1	52.147 ms	0.0 %
Seq Scan	1	52.147 ms	100.0 %
sms_taluka_mst	1	107.710 ms	0.0 %
Seq Scan	1	107.710 ms	100.0 %
sms_union_mst	1	0.020 ms	0.0 %
Seq Scan	1	0.020 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: kfMB : without sorting

Colorize:

Columns visibility:

Per node type stats

Per table stats