explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X1Ca : Optimization for: Lookups; plan #gWgB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 43.618 203.023 ↓ 2.0 2 1

Nested Loop Anti Join (cost=2,338.74..2,338.81 rows=1 width=3,144) (actual time=158.779..203.023 rows=2 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: 106752
2.          

CTE tdlookups

3. 0.209 1.051 ↓ 74.0 74 1

Hash Join (cost=1,261.14..2,321.50 rows=1 width=45) (actual time=0.706..1.051 rows=74 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.164 0.209 ↑ 1.1 353 1

Bitmap Heap Scan on mls_columns mp (cost=11.36..1,064.93 rows=380 width=42) (actual time=0.055..0.209 rows=353 loops=1)

  • Recheck Cond: (job_sid = '9'::numeric)
  • Heap Blocks: exact=40
5. 0.045 0.045 ↑ 1.1 353 1

Bitmap Index Scan on mls_columns_idx1 (cost=0.00..11.27 rows=380 width=0) (actual time=0.045..0.045 rows=353 loops=1)

  • Index Cond: (job_sid = '9'::numeric)
6. 0.225 0.633 ↓ 1.8 543 1

Hash (cost=1,244.41..1,244.41 rows=307 width=35) (actual time=0.633..0.633 rows=543 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
7. 0.332 0.408 ↓ 1.8 543 1

Bitmap Heap Scan on rets_table rets (cost=16.23..1,244.41 rows=307 width=35) (actual time=0.087..0.408 rows=543 loops=1)

  • Recheck Cond: (((mls_sid = '9'::numeric) AND ((interpretation)::text = 'Lookup'::text)) OR ((mls_sid = '9'::numeric) AND ((interpretation)::text = 'LookupMulti'::text)))
  • Heap Blocks: exact=91
8. 0.001 0.076 ↓ 0.0 0 1

BitmapOr (cost=16.23..16.23 rows=323 width=0) (actual time=0.076..0.076 rows=0 loops=1)

9. 0.046 0.046 ↓ 1.3 309 1

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..10.82 rows=240 width=0) (actual time=0.046..0.046 rows=309 loops=1)

  • Index Cond: ((mls_sid = '9'::numeric) AND ((interpretation)::text = 'Lookup'::text))
10. 0.029 0.029 ↓ 2.8 234 1

Bitmap Index Scan on rets_table_interpretation_idx (cost=0.00..5.25 rows=83 width=0) (actual time=0.029..0.029 rows=234 loops=1)

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

CTE prodlookups

12. 25.827 58.884 ↓ 53,376.0 53,376 1

Nested Loop (cost=0.56..8.62 rows=1 width=280) (actual time=0.022..58.884 rows=53,376 loops=1)

13. 0.127 0.127 ↓ 74.0 74 1

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

  • Filter: (job_sid = '9'::numeric)
14. 32.930 32.930 ↓ 721.0 721 74

Index Scan using rets_lookup_pkey on rets_lookup a_1 (cost=0.56..8.59 rows=1 width=62) (actual time=0.013..0.445 rows=721 loops=74)

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

CTE templookups

16. 0.056 39.728 ↓ 2.0 2 1

Nested Loop (cost=0.56..8.63 rows=1 width=84) (actual time=34.201..39.728 rows=2 loops=1)

17. 1.044 1.044 ↓ 74.0 74 1

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

  • Filter: (job_sid = '9'::numeric)
18. 38.628 38.628 ↓ 0.0 0 74

Index Scan using rets_lookup_pk on rets_lookup a_2 (cost=0.56..8.60 rows=1 width=84) (actual time=0.513..0.522 rows=0 loops=74)

  • Index Cond: ((mls_sid = '9'::numeric) AND ((resourceid)::text = (b_2.rets_resource)::text) AND ((classname)::text = (b_2.rets_class)::text) AND ((systemname)::text = (b_2.systemname)::text))
  • Filter: (created_date >= (current_date - '5 days'::interval))
  • Rows Removed by Filter: 721
19. 39.735 39.735 ↓ 2.0 2 1

CTE Scan on templookups a (cost=0.00..0.02 rows=1 width=3,144) (actual time=34.206..39.735 rows=2 loops=1)

  • Filter: (mls_sid = '9'::numeric)
20. 119.670 119.670 ↓ 53,376.0 53,376 2

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

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