#	exclusive	inclusive	rows x	rows	loops	node
1.	204.230	241529.367	↓ 117319.0	117319	1	→ Nested Loop (cost=667944.75..1454204.40 rows=1 width=39) (actual time=184565.237..241529.367 rows=117319 loops=1)
2.	194.407	197095.874	↓ 117319.0	117319	1	→ Nested Loop (cost=667944.75..1454203.33 rows=1 width=18) (actual time=184565.209..197095.874 rows=117319 loops=1)
3.	273.418	193576.081	↓ 144582.0	144582	1	→ Nested Loop (cost=667944.75..1454199.45 rows=1 width=63) (actual time=184565.163..193576.081 rows=144582 loops=1) Join Filter: (x.r = (min(x.r)))
4.	224.037	185206.071	↑ 1.7	144582	1	→ GroupAggregate (cost=667944.75..674178.50 rows=249350 width=15) (actual time=184565.108..185206.071 rows=144582 loops=1)
5.	1602.118	184982.034	↑ 1.7	144643	1	→ Sort (cost=667944.75..668568.12 rows=249350 width=15) (actual time=184565.100..184982.034 rows=144643 loops=1) Sort Key: x.gsc_id Sort Method: external merge Disk: 3664kB
6.	286.136	183379.916	↑ 1.7	144643	1	→ Nested Loop (cost=0.00..643157.25 rows=249350 width=15) (actual time=42.830..183379.916 rows=144643 loops=1)
7.	138.462	138.462	↑ 1.7	143607	1	→ Index Scan using o_recal_2_idx on o_recal_2_phot moo (cost=0.00..7126.66 rows=249350 width=8) (actual time=42.763..138.462 rows=143607 loops=1) Index Cond: (abs((poss_mag_recal_2 - sloan_poss_mag)) >= 0.75::double precision)
8.	182955.318	182955.318	↑ 1.0	1	143607	→ Index Scan using gsc_xmatch_final_objid_idx on gsc_xmatch_final x (cost=0.00..2.54 rows=1 width=23) (actual time=1.271..1.274 rows=1 loops=143607) Index Cond: (x.objid = moo.objid)
9.	8096.592	8096.592	↑ 1.0	1	144582	→ Index Scan using gsc_xmatch_final_gscid_idx on gsc_xmatch_final x (cost=0.00..3.10 rows=1 width=23) (actual time=0.056..0.056 rows=1 loops=144582) Index Cond: (x.gsc_id = x.gsc_id)
10.	3325.386	3325.386	↑ 1.0	1	144582	→ Index Scan using gsc_short_id_idx on gsc_short (cost=0.00..3.86 rows=1 width=21) (actual time=0.023..0.023 rows=1 loops=144582) Index Cond: (gsc_short.gsc_id = x.gsc_id) Filter: (gsc_short.b_mag IS NOT NULL)
11.	44229.263	44229.263	↑ 1.0	1	117319	→ Index Scan using o_recal_2_objid_idx on o_recal_2_phot foo (cost=0.00..1.06 rows=1 width=29) (actual time=0.376..0.377 rows=1 loops=117319) Index Cond: (foo.objid = x.objid)

"Nested Loop  (cost=667944.75..1454204.40 rows=1 width=39) (actual time=184565.237..241529.367 rows=117319 loops=1)"
"  ->  Nested Loop  (cost=667944.75..1454203.33 rows=1 width=18) (actual time=184565.209..197095.874 rows=117319 loops=1)"
"        ->  Nested Loop  (cost=667944.75..1454199.45 rows=1 width=63) (actual time=184565.163..193576.081 rows=144582 loops=1)"
"              Join Filter: (x.r = (min(x.r)))"
"              ->  GroupAggregate  (cost=667944.75..674178.50 rows=249350 width=15) (actual time=184565.108..185206.071 rows=144582 loops=1)"
"                    ->  Sort  (cost=667944.75..668568.12 rows=249350 width=15) (actual time=184565.100..184982.034 rows=144643 loops=1)"
"                          Sort Key: x.gsc_id"
"                          Sort Method:  external merge  Disk: 3664kB"
"                          ->  Nested Loop  (cost=0.00..643157.25 rows=249350 width=15) (actual time=42.830..183379.916 rows=144643 loops=1)"
"                                ->  Index Scan using o_recal_2_idx on o_recal_2_phot moo  (cost=0.00..7126.66 rows=249350 width=8) (actual time=42.763..138.462 rows=143607 loops=1)"
"                                      Index Cond: (abs((poss_mag_recal_2 - sloan_poss_mag)) >= 0.75::double precision)"
"                                ->  Index Scan using gsc_xmatch_final_objid_idx on gsc_xmatch_final x  (cost=0.00..2.54 rows=1 width=23) (actual time=1.271..1.274 rows=1 loops=143607)"
"                                      Index Cond: (x.objid = moo.objid)"
"              ->  Index Scan using gsc_xmatch_final_gscid_idx on gsc_xmatch_final x  (cost=0.00..3.10 rows=1 width=23) (actual time=0.056..0.056 rows=1 loops=144582)"
"                    Index Cond: (x.gsc_id = x.gsc_id)"
"        ->  Index Scan using gsc_short_id_idx on gsc_short  (cost=0.00..3.86 rows=1 width=21) (actual time=0.023..0.023 rows=1 loops=144582)"
"              Index Cond: (gsc_short.gsc_id = x.gsc_id)"
"              Filter: (gsc_short.b_mag IS NOT NULL)"
"  ->  Index Scan using o_recal_2_objid_idx on o_recal_2_phot foo  (cost=0.00..1.06 rows=1 width=29) (actual time=0.376..0.377 rows=1 loops=117319)"
"        Index Cond: (foo.objid = x.objid)"

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	224.037 ms	0.1 %
Index Scan	5	238745.021 ms	98.8 %
Nested Loop	4	958.191 ms	0.4 %
Sort	1	1602.118 ms	0.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
gsc_short	1	3325.386 ms	1.4 %
Index Scan	1	3325.386 ms	100.0 %
gsc_xmatch_final	2	191051.910 ms	79.1 %
Index Scan	2	191051.910 ms	100.0 %
o_recal_2_phot	2	44367.725 ms	18.4 %
Index Scan	2	44367.725 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 23w

Colorize:

Columns visibility:

Per node type stats

Per table stats