explain.depesz.com

A tool for finding a real cause for slow queries.

Result: jdn

options
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0
→

Limit (cost=309084967.35..309084967.42 rows=1 width=144) (actual time=.. rows= loops=)

2. 0.000 0.000 ↓ 0.0
→

GroupAggregate (cost=309084967.35..374551053.97 rows=872881155 width=144) (actual time=.. rows= loops=)

3. 0.000 0.000 ↓ 0.0
→

Sort (cost=309084967.35..311267170.24 rows=872881155 width=144) (actual time=.. rows= loops=)

  • Sort Key: n.nameid, (pg_stat_get_numscans(c.oid)), (pg_stat_get_tuples_returned(c.oid)), ((sum(pg_stat_get_numscans(i.indexrelid)))::bigint), (((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid))), (pg_stat_get_tuples_inserted(c.oid)), (pg_stat_get_tuples_updated(c.oid)), (pg_stat_get_tuples_hot_updated(c.oid)), (pg_stat_get_tuples_deleted(c.oid)), it.heap_blks_read, it.heap_blks_hit, it.idx_blks_read, it.idx_blks_hit, it.toast_blks_read, it.toast_blks_hit, it.tidx_blks_read, it.tidx_blks_hit, c.oid, c.oid
4. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=376533.37..447994.90 rows=872881155 width=144) (actual time=.. rows= loops=)

  • Hash Cond: (c.oid = c.oid)
5. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=89323.16..135299.40 rows=185676 width=76) (actual time=.. rows= loops=)

  • Hash Cond: ((((n.nspname)::text || '.'::text) || (c.relname)::text) = n.name)
6. 0.000 0.000 ↓ 0.0
→

Merge Left Join (cost=65631.77..89118.21 rows=185676 width=200) (actual time=.. rows= loops=)

  • Merge Cond: (c.oid = i.indrelid)
7. 0.000 0.000 ↓ 0.0
→

GroupAggregate (cost=65631.77..75775.89 rows=135255 width=136) (actual time=.. rows= loops=)

8. 0.000 0.000 ↓ 0.0
→

Sort (cost=65631.77..65969.91 rows=135255 width=136) (actual time=.. rows= loops=)

  • Sort Key: c.oid, n.nspname, c.relname
9. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=8866.36..47499.44 rows=135255 width=136) (actual time=.. rows= loops=)

  • Hash Cond: (c.relnamespace = n.oid)
10. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=8855.44..45628.77 rows=135255 width=76) (actual time=.. rows= loops=)

  • Hash Cond: (c.oid = i.indrelid)
11. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class c (cost=0.00..21894.12 rows=135255 width=72) (actual time=.. rows= loops=)

  • Filter: (relkind = ANY ('{r,t}'::"char"[]))
12. 0.000 0.000 ↓ 0.0
→

Hash (cost=5598.64..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

13. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_index i (cost=0.00..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

14. 0.000 0.000 ↓ 0.0
→

Hash (cost=7.63..7.63 rows=263 width=68) (actual time=.. rows= loops=)

15. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_namespace n (cost=0.00..7.63 rows=263 width=68) (actual time=.. rows= loops=)

16. 0.000 0.000 ↓ 0.0
→

Index Scan using pg_index_indrelid_index on pg_index i (cost=0.00..9298.71 rows=198464 width=8) (actual time=.. rows= loops=)

17. 0.000 0.000 ↓ 0.0
→

Hash (cost=15762.84..15762.84 rows=390284 width=36) (actual time=.. rows= loops=)

18. 0.000 0.000 ↓ 0.0
→

Seq Scan on pgstatspack_names n (cost=0.00..15762.84 rows=390284 width=36) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0
→

Hash (cost=263520.50..263520.50 rows=940217 width=76) (actual time=.. rows= loops=)

20. 0.000 0.000 ↓ 0.0
→

Merge Right Join (cost=244129.10..263520.50 rows=940217 width=76) (actual time=.. rows= loops=)

  • Merge Cond: (c.oid = c.reltoastrelid)
21. 0.000 0.000 ↓ 0.0
→

GroupAggregate (cost=46618.98..50861.80 rows=56571 width=136) (actual time=.. rows= loops=)

22. 0.000 0.000 ↓ 0.0
→

Sort (cost=46618.98..46760.40 rows=56571 width=136) (actual time=.. rows= loops=)

  • Sort Key: c.oid, n.nspname, c.relname
23. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=8865.76..39390.83 rows=56571 width=136) (actual time=.. rows= loops=)

  • Hash Cond: (c.oid = i.indrelid)
24. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=10.32..22977.36 rows=56571 width=132) (actual time=.. rows= loops=)

  • Hash Cond: (c.relnamespace = n.oid)
25. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class c (cost=0.00..21894.12 rows=135255 width=72) (actual time=.. rows= loops=)

  • Filter: (relkind = ANY ('{r,t}'::"char"[]))
26. 0.000 0.000 ↓ 0.0
→

Hash (cost=8.95..8.95 rows=110 width=68) (actual time=.. rows= loops=)

27. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_namespace n (cost=0.00..8.95 rows=110 width=68) (actual time=.. rows= loops=)

  • Filter: ((nspname = ANY ('{pg_catalog,information_schema}'::name[])) OR (nspname ~ '^pg_toast'::text))
28. 0.000 0.000 ↓ 0.0
→

Hash (cost=5598.64..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

29. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_index i (cost=0.00..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

30. 0.000 0.000 ↓ 0.0
→

Materialize (cost=197510.12..198186.40 rows=135255 width=76) (actual time=.. rows= loops=)

31. 0.000 0.000 ↓ 0.0
→

Sort (cost=197510.12..197848.26 rows=135255 width=76) (actual time=.. rows= loops=)

  • Sort Key: c.reltoastrelid
32. 0.000 0.000 ↓ 0.0
→

Hash Anti Join (cost=160766.22..181687.80 rows=135255 width=76) (actual time=.. rows= loops=)

  • Hash Cond: (c.oid = l.relation)
33. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=160748.71..179962.69 rows=135255 width=76) (actual time=.. rows= loops=)

  • Hash Cond: (it.relid = c.oid)
34. 0.000 0.000 ↓ 0.0
→

Subquery Scan on it (cost=134111.08..146284.03 rows=135255 width=68) (actual time=.. rows= loops=)

35. 0.000 0.000 ↓ 0.0
→

GroupAggregate (cost=134111.08..144931.48 rows=135255 width=144) (actual time=.. rows= loops=)

36. 0.000 0.000 ↓ 0.0
→

Sort (cost=134111.08..134449.22 rows=135255 width=144) (actual time=.. rows= loops=)

  • Sort Key: c.oid, n.nspname, c.relname, t.oid, x.oid
37. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=62141.61..115648.76 rows=135255 width=144) (actual time=.. rows= loops=)

  • Hash Cond: (c.relnamespace = n.oid)
38. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=62130.69..113778.08 rows=135255 width=84) (actual time=.. rows= loops=)

  • Hash Cond: (c.oid = i.indrelid)
39. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=53275.25..89779.44 rows=135255 width=80) (actual time=.. rows= loops=)

  • Hash Cond: (t.reltoastidxid = x.oid)
40. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=26637.62..55836.78 rows=135255 width=80) (actual time=.. rows= loops=)

  • Hash Cond: (c.reltoastrelid = t.oid)
41. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class c (cost=0.00..21894.12 rows=135255 width=76) (actual time=.. rows= loops=)

  • Filter: (relkind = ANY ('{r,t}'::"char"[]))
42. 0.000 0.000 ↓ 0.0
→

Hash (cost=21041.50..21041.50 rows=341050 width=8) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class t (cost=0.00..21041.50 rows=341050 width=8) (actual time=.. rows= loops=)

44. 0.000 0.000 ↓ 0.0
→

Hash (cost=21041.50..21041.50 rows=341050 width=4) (actual time=.. rows= loops=)

45. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class x (cost=0.00..21041.50 rows=341050 width=4) (actual time=.. rows= loops=)

46. 0.000 0.000 ↓ 0.0
→

Hash (cost=5598.64..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

47. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_index i (cost=0.00..5598.64 rows=198464 width=8) (actual time=.. rows= loops=)

48. 0.000 0.000 ↓ 0.0
→

Hash (cost=7.63..7.63 rows=263 width=68) (actual time=.. rows= loops=)

49. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_namespace n (cost=0.00..7.63 rows=263 width=68) (actual time=.. rows= loops=)

50. 0.000 0.000 ↓ 0.0
→

Hash (cost=21041.50..21041.50 rows=341050 width=8) (actual time=.. rows= loops=)

51. 0.000 0.000 ↓ 0.0
→

Seq Scan on pg_class c (cost=0.00..21041.50 rows=341050 width=8) (actual time=.. rows= loops=)

52. 0.000 0.000 ↓ 0.0
→

Hash (cost=17.50..17.50 rows=1 width=4) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0
→

Function Scan on pg_lock_status l (cost=0.00..17.50 rows=1 width=4) (actual time=.. rows= loops=)

  • Filter: ((locktype = 'relation'::text) AND (mode = ANY ('{AccessExclusiveLock,ShareRowExclusiveLock,ShareLock,ShareUpdateExclusiveLock}'::text[])))