explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZjXI

Settings
# exclusive inclusive rows x rows loops node
1. 16.362 32,828.721 ↑ 2,298.1 10,709 1

Result (cost=972.62..3,265,290.96 rows=24,610,300 width=224) (actual time=7.698..32,828.721 rows=10,709 loops=1)

2. 1.796 32,812.359 ↑ 2,298.1 10,709 1

Append (cost=972.62..2,465,456.21 rows=24,610,300 width=320) (actual time=7.689..32,812.359 rows=10,709 loops=1)

3. 0.031 7.841 ↓ 229.0 229 1

Subquery Scan on *SELECT* 1 (cost=972.62..972.65 rows=1 width=320) (actual time=7.688..7.841 rows=229 loops=1)

4. 0.078 7.810 ↓ 229.0 229 1

Unique (cost=972.62..972.64 rows=1 width=324) (actual time=7.688..7.810 rows=229 loops=1)

5. 0.459 7.732 ↓ 458.0 458 1

Sort (cost=972.62..972.62 rows=1 width=324) (actual time=7.685..7.732 rows=458 loops=1)

  • Sort Key: nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
  • Sort Method: quicksort Memory: 261kB
6. 0.775 7.273 ↓ 458.0 458 1

Nested Loop (cost=1.00..972.61 rows=1 width=324) (actual time=0.065..7.273 rows=458 loops=1)

  • Join Filter: (c.connamespace = nc.oid)
  • Rows Removed by Join Filter: 7328
7. 0.140 5.582 ↓ 458.0 458 1

Nested Loop (cost=1.00..971.23 rows=1 width=264) (actual time=0.060..5.582 rows=458 loops=1)

  • Join Filter: (r.oid = a.attrelid)
8. 0.848 4.526 ↓ 458.0 458 1

Nested Loop (cost=0.70..970.70 rows=1 width=212) (actual time=0.054..4.526 rows=458 loops=1)

  • Join Filter: (r.relnamespace = nr.oid)
  • Rows Removed by Join Filter: 7328
9. 0.002 2.762 ↓ 458.0 458 1

Nested Loop (cost=0.70..969.32 rows=1 width=152) (actual time=0.048..2.762 rows=458 loops=1)

10. 0.183 1.844 ↓ 50.9 458 1

Nested Loop (cost=0.42..964.82 rows=9 width=76) (actual time=0.036..1.844 rows=458 loops=1)

11. 1.199 1.199 ↓ 1.0 231 1

Seq Scan on pg_constraint c (cost=0.00..426.90 rows=224 width=72) (actual time=0.006..1.199 rows=231 loops=1)

  • Filter: (contype = 'c'::"char")
  • Rows Removed by Filter: 9560
12. 0.462 0.462 ↓ 2.0 2 231

Index Scan using pg_depend_depender_index on pg_depend d (cost=0.42..2.39 rows=1 width=12) (actual time=0.002..0.002 rows=2 loops=231)

  • Index Cond: ((classid = '2606'::oid) AND (objid = c.oid))
  • Filter: (refclassid = '1259'::oid)
  • Rows Removed by Filter: 0
13. 0.916 0.916 ↑ 1.0 1 458

Index Scan using pg_class_oid_index on pg_class r (cost=0.28..0.49 rows=1 width=76) (actual time=0.002..0.002 rows=1 loops=458)

  • Index Cond: (oid = d.refobjid)
  • Filter: (pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
14. 0.916 0.916 ↑ 1.0 17 458

Seq Scan on pg_namespace nr (cost=0.00..1.17 rows=17 width=68) (actual time=0.000..0.002 rows=17 loops=458)

15. 0.916 0.916 ↑ 1.0 1 458

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.29..0.51 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=458)

  • Index Cond: ((attrelid = d.refobjid) AND (attnum = d.refobjsubid))
  • Filter: (NOT attisdropped)
16. 0.916 0.916 ↑ 1.0 17 458

Seq Scan on pg_namespace nc (cost=0.00..1.17 rows=17 width=68) (actual time=0.000..0.002 rows=17 loops=458)

17. 2.077 32,802.722 ↑ 2,348.3 10,480 1

Subquery Scan on *SELECT* 2 (cost=3.06..2,464,483.56 rows=24,610,299 width=320) (actual time=6.424..32,802.722 rows=10,480 loops=1)

18. 21,796.002 32,800.645 ↑ 2,348.3 10,480 1

Nested Loop (cost=3.06..2,218,380.57 rows=24,610,299 width=324) (actual time=6.423..32,800.645 rows=10,480 loops=1)

  • Join Filter: CASE WHEN (c_1.contype = 'f'::"char") THEN ((r_1.oid = c_1.confrelid) AND (a_1.attnum = ANY (c_1.confkey))) ELSE ((r_1.oid = c_1.conrelid) AND (a_1.attnum = ANY (c_1.conkey))) END
  • Rows Removed by Join Filter: 203036210
19. 6.038 11.639 ↓ 1.0 9,526 1

Hash Join (cost=1.38..568.67 rows=9,338 width=183) (actual time=0.030..11.639 rows=9,526 loops=1)

  • Hash Cond: (c_1.connamespace = nc_1.oid)
20. 5.588 5.588 ↓ 1.0 9,526 1

Seq Scan on pg_constraint c_1 (cost=0.00..438.89 rows=9,338 width=123) (actual time=0.004..5.588 rows=9,526 loops=1)

  • Filter: (contype = ANY ('{p,u,f}'::"char"[]))
  • Rows Removed by Filter: 265
21. 0.010 0.013 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=68) (actual time=0.013..0.013 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.003 0.003 ↑ 1.0 17 1

Seq Scan on pg_namespace nc_1 (cost=0.00..1.17 rows=17 width=68) (actual time=0.001..0.003 rows=17 loops=1)

23. 10,980.171 10,993.004 ↓ 4.0 21,315 9,526

Materialize (cost=1.67..2,898.17 rows=5,271 width=198) (actual time=0.000..1.154 rows=21,315 loops=9,526)

24. 2.864 12.833 ↓ 4.0 21,315 1

Nested Loop (cost=1.67..2,871.81 rows=5,271 width=198) (actual time=0.034..12.833 rows=21,315 loops=1)

25. 0.236 3.099 ↓ 3.0 1,145 1

Hash Join (cost=1.38..1,582.57 rows=382 width=132) (actual time=0.028..3.099 rows=1,145 loops=1)

  • Hash Cond: (r_1.relnamespace = nr_1.oid)
26. 2.855 2.855 ↓ 3.0 1,145 1

Seq Scan on pg_class r_1 (cost=0.00..1,575.94 rows=382 width=72) (actual time=0.010..2.855 rows=1,145 loops=1)

  • Filter: (pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
  • Rows Removed by Filter: 4051
27. 0.005 0.008 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=68) (actual time=0.008..0.008 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.003 0.003 ↑ 1.0 17 1

Seq Scan on pg_namespace nr_1 (cost=0.00..1.17 rows=17 width=68) (actual time=0.001..0.003 rows=17 loops=1)

29. 6.870 6.870 ↓ 1.4 19 1,145

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1 (cost=0.29..3.23 rows=14 width=70) (actual time=0.002..0.006 rows=19 loops=1,145)

  • Index Cond: (attrelid = r_1.oid)