explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Pz

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

Sort (cost=25,422.25..25,425.50 rows=1,302 width=284) (actual rows= loops=)

  • Sort Key: (CASE WHEN ((COALESCE(cc.relpages, 0))::double precision < COALESCE(ceil(((cc.reltuples * ((((((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) + (foo.ma)::numeric) - CASE WHEN (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) = 0::numeric) THEN (foo.ma)::numeric ELSE ((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) END))::double precision + ((foo.maxfracsum * (((foo.nullhdr + foo.ma) - CASE WHEN ((foo.nullhdr % (foo.ma)::bigint) = 0) THEN (foo.ma)::bigint ELSE (foo.nullhdr % (foo.ma)::bigint) END))::double precision))) + 4::double precision)) / ((foo.bs)::double precision - 20::double precision))), 0::double precision)) THEN CASE WHEN ((COALESCE(c2.relpages, 0))::double precision < COALESCE(ceil(((c2.reltuples * (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) - 12::numeric))::double precision) / ((foo.bs)::double precision - 20::double precision))), 0::double precision)) THEN 0::numeric ELSE (COALESCE(foo.bs, 0::numeric) * ((COALESCE(c2.relpages, 0) - (COALESCE(ceil(((c2.reltuples * (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) - 12::numeric))::double precision) / ((foo.bs)::double precision - 20::double precision))), 0::double precision))::bigint))::numeric) END ELSE CASE WHEN ((COALESCE(c2.relpages, 0))::double precision < COALESCE(ceil(((c2.reltuples * (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) - 12::numeric))::double precision) / ((foo.bs)::double precision - 20::double precision))), 0::double precision)) THEN (COALESCE(foo.bs, 0::numeric) * ((COALESCE(cc.relpages, 0) - (COALESCE(ceil(((cc.reltuples * ((((((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) + (foo.ma)::numeric) - CASE WHEN (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) = 0::numeric) THEN (foo.ma)::numeric ELSE ((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) END))::double precision + ((foo.maxfracsum * (((foo.nullhdr + foo.ma) - CASE WHEN ((foo.nullhdr % (foo.ma)::bigint) = 0) THEN (foo.ma)::bigint ELSE (foo.nullhdr % (foo.ma)::bigint) END))::double precision))) + 4::double precision)) / ((foo.bs)::double precision - 20::double precision))), 0::double precision))::bigint))::numeric) ELSE (COALESCE(foo.bs, 0::numeric) * ((((COALESCE(cc.relpages, 0) - (COALESCE(ceil(((cc.reltuples * ((((((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) + (foo.ma)::numeric) - CASE WHEN (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) = 0::numeric) THEN (foo.ma)::numeric ELSE ((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) END))::double precision + ((foo.maxfracsum * (((foo.nullhdr + foo.ma) - CASE WHEN ((foo.nullhdr % (foo.ma)::bigint) = 0) THEN (foo.ma)::bigint ELSE (foo.nullhdr % (foo.ma)::bigint) END))::double precision))) + 4::double precision)) / ((foo.bs)::double precision - 20::double precision))), 0::double precision))::bigint) + COALESCE(c2.relpages, 0)) - (COALESCE(ceil(((c2.reltuples * (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) - 12::numeric))::double precision) / ((foo.bs)::double precision - 20::double precision))), 0::double precision))::bigint))::numeric) END END)
2. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,105.04..25,394.11 rows=1,302 width=284) (actual rows= loops=)

  • Hash Cond: ((foo.relname = cc.relname) AND (foo.nspname = nn.nspname))
  • Filter: ((((COALESCE(cc.relpages, 0))::double precision - COALESCE(ceil(((cc.reltuples * ((((((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) + (foo.ma)::numeric) - CASE WHEN (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) = 0::numeric) THEN (foo.ma)::numeric ELSE ((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) % (foo.ma)::numeric) END))::double precision + ((foo.maxfracsum * (((foo.nullhdr + foo.ma) - CASE WHEN ((foo.nullhdr % (foo.ma)::bigint) = 0) THEN (foo.ma)::bigint ELSE (foo.nullhdr % (foo.ma)::bigint) END))::double precision))) + 4::double precision)) / ((foo.bs)::double precision - 20::double precision))), 0::double precision)) > 0::double precision) OR (((COALESCE(c2.relpages, 0))::double precision - COALESCE(ceil(((c2.reltuples * (((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.hdr % foo.ma) END))::double precision))::numeric) - 12::numeric))::double precision) / ((foo.bs)::double precision - 20::double precision))), 0::double precision)) > 10::double precision))
3. 0.000 0.000 ↓ 0.0

Subquery Scan on foo (cost=1,714.54..21,862.41 rows=2,120 width=204) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,714.54..21,841.21 rows=2,120 width=168) (actual rows= loops=)

5.          

Initplan (for HashAggregate)

6. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=761.24..1,661.52 rows=2,120 width=168) (actual rows= loops=)

  • Hash Cond: ((n.nspname = ns.nspname) AND (c.relname = tbl.relname) AND (a.attname = att.attname))
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..870.90 rows=10 width=200) (actual rows= loops=)

  • Join Filter: (c.relnamespace = n.oid)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..868.76 rows=10 width=140) (actual rows= loops=)

  • Join Filter: (s.starelid = c.oid)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..860.06 rows=30 width=82) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on pg_statistic s (cost=0.00..165.43 rows=922 width=14) (actual rows= loops=)

  • Filter: (NOT stainherit)
12. 0.000 0.000 ↓ 0.0

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.00..0.74 rows=1 width=70) (actual rows= loops=)

  • Index Cond: ((attrelid = s.starelid) AND (attnum = s.staattnum))
  • Filter: (NOT attisdropped)
13. 0.000 0.000 ↓ 0.0

Index Scan using pg_class_oid_index on pg_class c (cost=0.00..0.28 rows=1 width=72) (actual rows= loops=)

  • Index Cond: (oid = a.attrelid)
  • Filter: has_column_privilege(oid, a.attnum, 'select'::text)
14. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.10 rows=7 width=68) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace n (cost=0.00..1.07 rows=7 width=68) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=724.14..724.14 rows=2,120 width=224) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1,140kB
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=113.76..724.14 rows=2,120 width=224) (actual rows= loops=)

  • Hash Cond: (att.attrelid = tbl.oid)
18. 0.000 0.000 ↓ 0.0

Seq Scan on pg_attribute att (cost=0.00..549.38 rows=10,614 width=68) (actual rows= loops=)

  • Filter: (attnum > 0)
19. 0.000 0.000 ↓ 0.0

Hash (cost=106.93..106.93 rows=546 width=164) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 148kB
20. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.25..106.93 rows=546 width=164) (actual rows= loops=)

  • Hash Cond: (tbl.relnamespace = ns.oid)
21. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class tbl (cost=0.00..98.18 rows=546 width=72) (actual rows= loops=)

  • Filter: (relkind = 'r'::"char")
22. 0.000 0.000 ↓ 0.0

Hash (cost=1.16..1.16 rows=7 width=100) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1.16 rows=7 width=100) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace ns (cost=0.00..1.07 rows=7 width=68) (actual rows= loops=)

26.          

SubPlan (for HashAggregate)

27. 0.000 0.000 ↓ 0.0

Aggregate (cost=9.44..9.46 rows=1 width=0) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.44 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (c.oid = s.starelid)
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.14 rows=1 width=10) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..3.57 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (c.relnamespace = n.oid)
31. 0.000 0.000 ↓ 0.0

Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.00..2.47 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (relname = tbl.relname)
32. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=1 width=4) (actual rows= loops=)

  • Filter: (nspname = ns.nspname)
33. 0.000 0.000 ↓ 0.0

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.00..5.55 rows=2 width=6) (actual rows= loops=)

  • Index Cond: (attrelid = c.oid)
  • Filter: ((NOT attisdropped) AND has_column_privilege(c.oid, attnum, 'select'::text))
34. 0.000 0.000 ↓ 0.0

Index Scan using pg_statistic_relid_att_inh_index on pg_statistic s (cost=0.00..0.29 rows=1 width=6) (actual rows= loops=)

  • Index Cond: ((starelid = a.attrelid) AND (staattnum = a.attnum))
  • Filter: (stanullfrac <> 0::double precision)
35. 0.000 0.000 ↓ 0.0

Hash (cost=355.36..355.36 rows=2,343 width=208) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 632kB
36. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=219.85..355.36 rows=2,343 width=208) (actual rows= loops=)

  • Hash Cond: (i.indrelid = cc.oid)
37. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=64.38..180.36 rows=1,439 width=76) (actual rows= loops=)

  • Hash Cond: (c2.oid = i.indexrelid)
38. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class c2 (cost=0.00..91.34 rows=2,734 width=76) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=46.39..46.39 rows=1,439 width=8) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 57kB
40. 0.000 0.000 ↓ 0.0

Seq Scan on pg_index i (cost=0.00..46.39 rows=1,439 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=126.19..126.19 rows=2,343 width=140) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 449kB
42. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.16..126.19 rows=2,343 width=140) (actual rows= loops=)

  • Hash Cond: (cc.relnamespace = nn.oid)
43. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class cc (cost=0.00..91.34 rows=2,734 width=80) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=6 width=68) (actual rows= loops=)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
45. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace nn (cost=0.00..1.09 rows=6 width=68) (actual rows= loops=)

  • Filter: (nspname <> 'information_schema'::name)