explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tgIq : Lookups

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

Nested Loop Anti Join (cost=1,973.65..1,973.72 rows=1 width=3,136) (actual rows= loops=)

  • Join Filter: ((a.mls_sid = b.mls_sid) AND ((a.resourceid)::text = (b.resourceid)::text) AND ((a.classname)::text = (b.classname)::text) AND ((a.systemname)::text = (b.systemname)::text) AND ((a.value)::text = (b.value)::text))
2.          

CTE tdlookups

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,248.57..1,960.41 rows=1 width=45) (actual rows= loops=)

  • Hash Cond: (((mp.rets_resource)::text = (rets.resourceid)::text) AND ((mp.rets_class)::text = (rets.classname)::text) AND ((mp.rets_column)::text = (rets.systemname)::text))
4. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mls_columns mp (cost=6.22..713.91 rows=232 width=42) (actual rows= loops=)

  • Recheck Cond: (job_sid = '163'::numeric)
5. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mls_columns_idx1 (cost=0.00..6.16 rows=232 width=0) (actual rows= loops=)

  • Index Cond: (job_sid = '163'::numeric)
6. 0.000 0.000 ↓ 0.0

Hash (cost=1,236.99..1,236.99 rows=306 width=35) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on rets_table rets (cost=16.21..1,236.99 rows=306 width=35) (actual rows= loops=)

  • Recheck Cond: (((mls_sid = '163'::numeric) AND ((interpretation)::text = 'Lookup'::text)) OR ((mls_sid = '163'::numeric) AND ((interpretation)::text = 'LookupMulti'::text)))
8. 0.000 0.000 ↓ 0.0

BitmapOr (cost=16.21..16.21 rows=321 width=0) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..10.83 rows=241 width=0) (actual rows= loops=)

  • Index Cond: ((mls_sid = '163'::numeric) AND ((interpretation)::text = 'Lookup'::text))
10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..5.22 rows=80 width=0) (actual rows= loops=)

  • Index Cond: ((mls_sid = '163'::numeric) AND ((interpretation)::text = 'LookupMulti'::text))
11.          

CTE prodlookups

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..4.62 rows=1 width=263) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

CTE Scan on tdlookups b_1 (cost=0.00..0.02 rows=1 width=1,004) (actual rows= loops=)

  • Filter: (job_sid = '163'::numeric)
14. 0.000 0.000 ↓ 0.0

Index Only Scan using rets_lookup_pkey on rets_lookup a_1 (cost=0.56..4.59 rows=1 width=45) (actual rows= loops=)

  • Index Cond: ((mls_sid = '163'::numeric) AND (resourceid = (b_1.rets_resource)::text) AND (classname = (b_1.rets_class)::text) AND (systemname = (b_1.systemname)::text))
15.          

CTE templookups

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..8.62 rows=1 width=77) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on tdlookups b_2 (cost=0.00..0.02 rows=1 width=786) (actual rows= loops=)

  • Filter: (job_sid = '163'::numeric)
18. 0.000 0.000 ↓ 0.0

Index Scan using rets_lookup_pk on rets_lookup a_2 (cost=0.56..8.59 rows=1 width=77) (actual rows= loops=)

  • Index Cond: ((mls_sid = '163'::numeric) AND ((resourceid)::text = (b_2.rets_resource)::text) AND ((classname)::text = (b_2.rets_class)::text) AND ((systemname)::text = (b_2.systemname)::text))
19. 0.000 0.000 ↓ 0.0

CTE Scan on templookups a (cost=0.00..0.02 rows=1 width=3,136) (actual rows= loops=)

  • Filter: (mls_sid = '163'::numeric)
20. 0.000 0.000 ↓ 0.0

CTE Scan on prodlookups b (cost=0.00..0.02 rows=1 width=2,096) (actual rows= loops=)

  • Filter: (mls_sid = '163'::numeric)