#	exclusive	inclusive	rows x	rows	loops	node
1.	47.998	4813.598	↓ 38704.0	38704	1	→ Nested Loop (cost=639445.31..1387547.31 rows=1 width=37) (actual time=955.215..4813.598 rows=38704 loops=1)
2.	36.152	3739.771	↓ 48849.0	48849	1	→ Nested Loop (cost=639445.31..1387543.43 rows=1 width=84) (actual time=955.170..3739.771 rows=48849 loops=1)
3.	65.192	2580.092	↓ 48849.0	48849	1	→ Nested Loop (cost=639445.31..1387541.51 rows=1 width=84) (actual time=955.120..2580.092 rows=48849 loops=1)
4.	69.240	2172.957	↓ 48849.0	48849	1	→ Nested Loop (cost=639445.31..1387540.45 rows=1 width=63) (actual time=955.091..2172.957 rows=48849 loops=1) Join Filter: (x.r = (min(x.r)))
5.	53.096	1077.888	↑ 4.8	48849	1	→ GroupAggregate (cost=639445.31..645326.46 rows=235246 width=15) (actual time=954.945..1077.888 rows=48849 loops=1)
6.	319.438	1024.792	↑ 4.8	48868	1	→ Sort (cost=639445.31..640033.42 rows=235246 width=15) (actual time=954.938..1024.792 rows=48868 loops=1) Sort Key: x.gsc_id Sort Method: external merge Disk: 1240kB
7.	56.345	705.354	↑ 4.8	48868	1	→ Nested Loop (cost=0.00..616158.88 rows=235246 width=15) (actual time=0.090..705.354 rows=48868 loops=1)
8.	16.546	16.546	↑ 4.8	48651	1	→ Index Scan using o_recal_2_idx on o_recal_2_phot foo (cost=0.00..6724.84 rows=235246 width=8) (actual time=0.050..16.546 rows=48651 loops=1) Index Cond: (abs((poss_mag_recal_2 - sloan_poss_mag)) >= 1::double precision)
9.	632.463	632.463	↑ 1.0	1	48651	→ Index Scan using gsc_xmatch_final_objid_idx on gsc_xmatch_final x (cost=0.00..2.58 rows=1 width=23) (actual time=0.013..0.013 rows=1 loops=48651) Index Cond: (x.objid = foo.objid)
10.	1025.829	1025.829	↑ 1.0	1	48849	→ Index Scan using gsc_xmatch_final_gscid_idx on gsc_xmatch_final x (cost=0.00..3.13 rows=1 width=23) (actual time=0.021..0.021 rows=1 loops=48849) Index Cond: (x.gsc_id = x.gsc_id)
11.	341.943	341.943	↑ 1.0	1	48849	→ 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.007..0.007 rows=1 loops=48849) Index Cond: (foo.objid = x.objid)
12.	1123.527	1123.527	↑ 1.0	1	48849	→ Index Scan using usno_merged_id_idx on usno_merged (cost=0.00..1.90 rows=1 width=13) (actual time=0.023..0.023 rows=1 loops=48849) Index Cond: (usno_merged.usno_id = foo.usno_id) Filter: (usno_merged.b1_surv = 0)
13.	1025.829	1025.829	↑ 1.0	1	48849	→ Index Scan using gsc_short_id_idx on gsc_short (cost=0.00..3.87 rows=1 width=19) (actual time=0.021..0.021 rows=1 loops=48849) Index Cond: (gsc_short.gsc_id = x.gsc_id) Filter: ((gsc_short.b_mag_err < 1::double precision) AND (gsc_short.b_mag_code = 7))

"Nested Loop  (cost=639445.31..1387547.31 rows=1 width=37) (actual time=955.215..4813.598 rows=38704 loops=1)"
"  ->  Nested Loop  (cost=639445.31..1387543.43 rows=1 width=84) (actual time=955.170..3739.771 rows=48849 loops=1)"
"        ->  Nested Loop  (cost=639445.31..1387541.51 rows=1 width=84) (actual time=955.120..2580.092 rows=48849 loops=1)"
"              ->  Nested Loop  (cost=639445.31..1387540.45 rows=1 width=63) (actual time=955.091..2172.957 rows=48849 loops=1)"
"                    Join Filter: (x.r = (min(x.r)))"
"                    ->  GroupAggregate  (cost=639445.31..645326.46 rows=235246 width=15) (actual time=954.945..1077.888 rows=48849 loops=1)"
"                          ->  Sort  (cost=639445.31..640033.42 rows=235246 width=15) (actual time=954.938..1024.792 rows=48868 loops=1)"
"                                Sort Key: x.gsc_id"
"                                Sort Method:  external merge  Disk: 1240kB"
"                                ->  Nested Loop  (cost=0.00..616158.88 rows=235246 width=15) (actual time=0.090..705.354 rows=48868 loops=1)"
"                                      ->  Index Scan using o_recal_2_idx on o_recal_2_phot foo  (cost=0.00..6724.84 rows=235246 width=8) (actual time=0.050..16.546 rows=48651 loops=1)"
"                                            Index Cond: (abs((poss_mag_recal_2 - sloan_poss_mag)) >= 1::double precision)"
"                                      ->  Index Scan using gsc_xmatch_final_objid_idx on gsc_xmatch_final x  (cost=0.00..2.58 rows=1 width=23) (actual time=0.013..0.013 rows=1 loops=48651)"
"                                            Index Cond: (x.objid = foo.objid)"
"                    ->  Index Scan using gsc_xmatch_final_gscid_idx on gsc_xmatch_final x  (cost=0.00..3.13 rows=1 width=23) (actual time=0.021..0.021 rows=1 loops=48849)"
"                          Index Cond: (x.gsc_id = x.gsc_id)"
"              ->  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.007..0.007 rows=1 loops=48849)"
"                    Index Cond: (foo.objid = x.objid)"
"        ->  Index Scan using usno_merged_id_idx on usno_merged  (cost=0.00..1.90 rows=1 width=13) (actual time=0.023..0.023 rows=1 loops=48849)"
"              Index Cond: (usno_merged.usno_id = foo.usno_id)"
"              Filter: (usno_merged.b1_surv = 0)"
"  ->  Index Scan using gsc_short_id_idx on gsc_short  (cost=0.00..3.87 rows=1 width=19) (actual time=0.021..0.021 rows=1 loops=48849)"
"        Index Cond: (gsc_short.gsc_id = x.gsc_id)"
"        Filter: ((gsc_short.b_mag_err < 1::double precision) AND (gsc_short.b_mag_code = 7))"

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	53.096 ms	1.1 %
Index Scan	6	4166.137 ms	86.5 %
Nested Loop	5	274.927 ms	5.7 %
Sort	1	319.438 ms	6.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
gsc_short	1	1025.829 ms	21.3 %
Index Scan	1	1025.829 ms	100.0 %
gsc_xmatch_final	2	1658.292 ms	34.5 %
Index Scan	2	1658.292 ms	100.0 %
o_recal_2_phot	2	358.489 ms	7.4 %
Index Scan	2	358.489 ms	100.0 %
usno_merged	1	1123.527 ms	23.3 %
Index Scan	1	1123.527 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: gcP

Colorize:

Columns visibility:

Per node type stats

Per table stats