explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZH5A : Optimization for: plan #S2wQV

Settings
# exclusive inclusive rows x rows loops node
1. 0.341 17.579 ↓ 68.0 68 1

Sort (cost=258.97..258.98 rows=1 width=360) (actual time=17.570..17.579 rows=68 loops=1)

  • Sort Key: (CASE WHEN ((cc.relpages)::double precision < ceil(((cc.reltuples * (((((((foo.datawidth + (((foo.hdr + foo.ma) - CASE WHEN ((foo.hdr % foo.ma) = 0) THEN foo.ma ELSE (foo.h
  • Sort Method: quicksort Memory: 60kB
2. 6.105 17.238 ↓ 68.0 68 1

Nested Loop Left Join (cost=241.32..258.96 rows=1 width=360) (actual time=10.475..17.238 rows=68 loops=1)

3. 0.065 10.725 ↓ 68.0 68 1

Nested Loop Left Join (cost=241.05..257.44 rows=1 width=200) (actual time=9.987..10.725 rows=68 loops=1)

4. 0.052 10.520 ↓ 35.0 35 1

Nested Loop (cost=240.90..257.18 rows=1 width=200) (actual time=9.978..10.520 rows=35 loops=1)

  • Join Filter: (foo.schemaname = nn.nspname)
5. 0.172 10.263 ↓ 8.2 41 1

Hash Join (cost=240.77..256.17 rows=5 width=204) (actual time=9.965..10.263 rows=41 loops=1)

  • Hash Cond: (cc.relname = foo.tablename)
6. 0.170 0.170 ↑ 1.0 316 1

Seq Scan on pg_class cc (cost=0.00..14.16 rows=316 width=80) (actual time=0.011..0.170 rows=316 loops=1)

7. 0.075 9.921 ↓ 8.2 41 1

Hash (cost=240.71..240.71 rows=5 width=188) (actual time=9.921..9.921 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
8. 0.017 9.846 ↓ 8.2 41 1

Subquery Scan on foo (cost=138.45..240.71 rows=5 width=188) (actual time=7.782..9.846 rows=41 loops=1)

9. 0.490 9.829 ↓ 8.2 41 1

GroupAggregate (cost=138.45..240.66 rows=5 width=188) (actual time=7.782..9.829 rows=41 loops=1)

  • Group Key: s.schemaname, s.tablename, (CASE WHEN ("substring"(version(), 12, 3) = ANY ('{8.0,8.1,8.2}'::text[])) THEN 27 ELSE 23 END), (CASE
10.          

Initplan (forGroupAggregate)

11. 0.024 0.024 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=1)

12. 0.679 7.675 ↓ 76.8 384 1

Sort (cost=138.43..138.44 rows=5 width=176) (actual time=7.617..7.675 rows=384 loops=1)

  • Sort Key: s.schemaname, s.tablename
  • Sort Method: quicksort Memory: 127kB
13. 2.093 6.996 ↓ 76.8 384 1

Subquery Scan on s (cost=49.91..138.37 rows=5 width=176) (actual time=1.111..6.996 rows=384 loops=1)

14. 1.622 4.903 ↓ 76.8 384 1

Nested Loop Left Join (cost=49.91..138.25 rows=5 width=401) (actual time=1.020..4.903 rows=384 loops=1)

  • -> Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.13..0.19 rows=1 width=68) (actual time=0.003..0.003
15. 1.418 3.281 ↓ 76.8 384 1

Hash Join (cost=49.78..137.26 rows=5 width=76) (actual time=1.007..3.281 rows=384 loops=1)

  • Hash Cond: ((a.attrelid = c.oid) AND (a.attnum = s_1.staattnum))
  • Join Filter: has_column_privilege(c.oid, a.attnum, 'select'::text)
  • Index Cond: (oid = c.relnamespace)
16. 0.908 0.908 ↑ 1.0 2,417 1

Seq Scan on pg_attribute a (cost=0.00..69.17 rows=2,417 width=6) (actual time=0.008..0.908 rows=2,417 loops=1)

  • Filter: (NOT attisdropped)
17. 0.311 0.955 ↑ 1.0 384 1

Hash (cost=44.02..44.02 rows=384 width=86) (actual time=0.955..0.955 rows=384 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
18. 0.341 0.644 ↑ 1.0 384 1

Hash Join (cost=18.90..44.02 rows=384 width=86) (actual time=0.329..0.644 rows=384 loops=1)

  • Hash Cond: (s_1.starelid = c.oid)
  • -> Seq Scan on pg_statistic s_1 (cost=0.00..19.84 rows=384 width=14) (actual time=0.005..0.115 rows=38
19. 0.303 0.303 ↑ 1.0 316 1

Hash (cost=14.95..14.95 rows=316 width=72) (actual time=0.303..0.303 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • -> Seq Scan on pg_class c (cost=0.00..14.95 rows=316 width=72) (actual time=0.005..0.173 rows=31
  • Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid)))
20.          

SubPlan (forGroupAggregate)

21. 0.082 1.640 ↑ 1.0 1 41

Aggregate (cost=20.36..20.38 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=41)

22. 0.187 1.558 ↑ 1.0 1 41

Nested Loop (cost=4.72..20.35 rows=1 width=401) (actual time=0.029..0.038 rows=1 loops=41)

  • Join Filter: (c_1.relnamespace = n_1.oid)
23. 0.369 1.271 ↑ 1.0 1 41

Nested Loop (cost=4.72..19.26 rows=1 width=4) (actual time=0.026..0.031 rows=1 loops=41)

  • Join Filter: (has_column_privilege(c_1.oid, a_1.attnum, 'select'::text) AND (c_1.oid = a_1.attrelid))
  • -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1 (cost=0.28..0.87 rows=1 width=6) (actual time=
24. 0.902 0.902 ↑ 1.0 1 41

Nested Loop (cost=4.44..18.38 rows=1 width=14) (actual time=0.020..0.022 rows=1 loops=41)

  • -> Index Scan using pg_class_relname_nsp_index on pg_class c_1 (cost=0.27..8.29 rows=1 width=8) (actual time=0.0
  • Index Cond: (relname = s.tablename)
  • Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid)))
  • -> Bitmap Heap Scan on pg_statistic s_2 (cost=4.16..10.07 rows=2 width=6) (actual time=0.011..0.011 rows=1 loops
  • Recheck Cond: (starelid = c_1.oid)
  • Filter: (stanullfrac <> '0'::double precision)
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=60
  • -> Bitmap Index Scan on pg_statistic_relid_att_inh_index (cost=0.00..4.16 rows=2 width=0) (actual time=0.0
  • Index Cond: (starelid = c_1.oid)
  • Index Cond: ((attrelid = s_2.starelid) AND (attnum = s_2.staattnum))
  • Filter: (NOT attisdropped)
25. 0.100 0.100 ↑ 1.0 1 50

Seq Scan on pg_namespace n_1 (cost=0.00..1.07 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)

  • Filter: (nspname = s.schemaname)
  • Rows Removed by Filter: 5
26. 0.205 0.205 ↑ 1.0 1 41

Index Scan using pg_namespace_oid_index on pg_namespace nn (cost=0.13..0.19 rows=1 width=68) (actual time=0.004..0.005 rows=1 loops=41)

  • Index Cond: (oid = cc.relnamespace)
  • Filter: (nspname <> 'information_schema'::name)
  • Rows Removed by Filter: 0
27. 0.140 0.140 ↑ 1.0 2 35

Index Scan using pg_index_indrelid_index on pg_index i (cost=0.14..0.24 rows=2 width=8) (actual time=0.002..0.004 rows=2 loops=35)

  • Index Cond: (indrelid = cc.oid)
28. 0.408 0.408 ↑ 1.0 1 68

Index Scan using pg_class_oid_index on pg_class c2 (cost=0.27..0.79 rows=1 width=76) (actual time=0.005..0.006 rows=1 loops=68)

  • Index Cond: (oid = i.indexrelid)