explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gWgB : Lookups

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 430,948.528 804,619.340 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1,276.63..1,276.69 rows=1 width=3,136) (actual time=804,619.340..804,619.340 rows=0 loops=1)

  • 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))
  • Rows Removed by Join Filter: 1166303716
2.          

CTE tdlookups

3. 0.454 1.311 ↓ 61.0 61 1

Hash Join (cost=661.23..1,269.39 rows=1 width=45) (actual time=0.673..1.311 rows=61 loops=1)

  • 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.206 0.244 ↑ 1.1 353 1

Bitmap Heap Scan on mls_columns mp (cost=7.42..608.66 rows=387 width=42) (actual time=0.045..0.244 rows=353 loops=1)

  • Recheck Cond: (job_sid = '163'::numeric)
  • Heap Blocks: exact=10
5. 0.038 0.038 ↑ 1.1 353 1

Bitmap Index Scan on mls_columns_idx1 (cost=0.00..7.32 rows=387 width=0) (actual time=0.038..0.038 rows=353 loops=1)

  • Index Cond: (job_sid = '163'::numeric)
6. 0.239 0.613 ↓ 1.6 509 1

Hash (cost=648.33..648.33 rows=313 width=35) (actual time=0.613..0.613 rows=509 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
7. 0.299 0.374 ↓ 1.6 509 1

Bitmap Heap Scan on rets_table rets (cost=10.29..648.33 rows=313 width=35) (actual time=0.087..0.374 rows=509 loops=1)

  • Recheck Cond: (((mls_sid = '163'::numeric) AND ((interpretation)::text = 'Lookup'::text)) OR ((mls_sid = '163'::numeric) AND ((interpretation)::text = 'LookupMulti'::text)))
  • Heap Blocks: exact=95
8. 0.000 0.075 ↓ 0.0 0 1

BitmapOr (cost=10.29..10.29 rows=328 width=0) (actual time=0.075..0.075 rows=0 loops=1)

9. 0.047 0.047 ↓ 1.2 306 1

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..6.89 rows=247 width=0) (actual time=0.047..0.047 rows=306 loops=1)

  • Index Cond: ((mls_sid = '163'::numeric) AND ((interpretation)::text = 'Lookup'::text))
10. 0.028 0.028 ↓ 2.5 203 1

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..3.24 rows=82 width=0) (actual time=0.028..0.028 rows=203 loops=1)

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

CTE prodlookups

12. 34.384 128.208 ↓ 48,298.0 48,298 1

Nested Loop (cost=0.56..2.62 rows=1 width=263) (actual time=0.042..128.208 rows=48,298 loops=1)

13. 0.067 0.067 ↓ 61.0 61 1

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

  • Filter: (job_sid = '163'::numeric)
14. 93.757 93.757 ↓ 792.0 792 61

Index Only Scan using rets_lookup_pkey on rets_lookup a_1 (cost=0.56..2.59 rows=1 width=45) (actual time=0.048..1.537 rows=792 loops=61)

  • 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))
  • Heap Fetches: 0
15.          

CTE templookups

16. 50.902 203.367 ↓ 48,298.0 48,298 1

Nested Loop (cost=0.56..4.62 rows=1 width=77) (actual time=0.713..203.367 rows=48,298 loops=1)

17. 1.429 1.429 ↓ 61.0 61 1

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

  • Filter: (job_sid = '163'::numeric)
18. 151.036 151.036 ↓ 792.0 792 61

Index Scan using rets_lookup_pk on rets_lookup a_2 (cost=0.56..4.59 rows=1 width=77) (actual time=0.026..2.476 rows=792 loops=61)

  • 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. 278.974 278.974 ↓ 48,298.0 48,298 1

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

  • Filter: (mls_sid = '163'::numeric)
20. 373,391.838 373,391.838 ↓ 24,149.0 24,149 48,298

CTE Scan on prodlookups b (cost=0.00..0.02 rows=1 width=2,096) (actual time=0.000..7.731 rows=24,149 loops=48,298)

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