explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Og7G : Optimization for: plan #wzlk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.004 26.866 ↓ 0.0 0 1

Sort (cost=9,736.86..9,736.87 rows=1 width=852) (actual time=26.866..26.866 rows=0 loops=1)

  • Sort Key: integrationauditview.createddate
  • Sort Method: quicksort Memory: 25kB
2. 0.000 26.862 ↓ 0.0 0 1

Subquery Scan on integrationauditview (cost=9,506.38..9,736.85 rows=1 width=852) (actual time=26.862..26.862 rows=0 loops=1)

  • Filter: (lower((integrationauditview.tenantdestination)::text) ~~ '%mr000179%'::text)
3. 0.001 26.862 ↓ 0.0 0 1

Append (cost=9,506.38..9,720.89 rows=1,064 width=215) (actual time=26.862..26.862 rows=0 loops=1)

4. 0.000 26.738 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=9,506.38..9,559.53 rows=1,063 width=215) (actual time=26.738..26.738 rows=0 loops=1)

5. 0.004 26.738 ↓ 0.0 0 1

HashAggregate (cost=9,506.38..9,548.90 rows=1,063 width=215) (actual time=26.738..26.738 rows=0 loops=1)

  • Group Key: ia.transactiondate, ia.createddate, ia.tenantname, ia.auditguid
  • Filter: ((lower((max(CASE WHEN ((dt.field)::bpchar = 'Message Id'::bpchar) THEN (dt.fieldvalue)::bpchar ELSE NULL::bpchar END))::text) ~~ '%1234%'::text) OR (lower((max(CASE WHEN ((dt.field)::bpchar = 'Receiving Provider'::bpchar) (...)
6. 0.005 26.734 ↓ 0.0 0 1

Hash Join (cost=8,860.93..9,445.26 rows=1,063 width=215) (actual time=26.734..26.734 rows=0 loops=1)

  • Hash Cond: (dt.auditguid = ia.auditguid)
7. 0.003 0.003 ↑ 17,180.0 1 1

Seq Scan on auditdetails dt (cost=0.00..487.80 rows=17,180 width=65) (actual time=0.003..0.003 rows=1 loops=1)

8. 0.000 26.726 ↓ 0.0 0 1

Hash (cost=8,832.34..8,832.34 rows=2,287 width=187) (actual time=26.726..26.726 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
9. 26.726 26.726 ↓ 0.0 0 1

Seq Scan on integrationaudit ia (cost=0.00..8,832.34 rows=2,287 width=187) (actual time=26.726..26.726 rows=0 loops=1)

  • Filter: ((transactiontype ~~ 'SCM%'::text) AND ((responsecode)::bpchar = '200'::bpchar) AND ((transactiondate)::date >= '2018-10-31'::date))
  • Rows Removed by Filter: 36967
10. 0.000 0.123 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=161.33..161.36 rows=1 width=241) (actual time=0.123..0.123 rows=0 loops=1)

11. 0.001 0.123 ↓ 0.0 0 1

HashAggregate (cost=161.33..161.35 rows=1 width=241) (actual time=0.123..0.123 rows=0 loops=1)

  • Group Key: integrationaudit.transactiondate, integrationaudit.createddate, integrationaudit.tenantname, integrationaudit.auditguid
12. 0.122 0.122 ↓ 0.0 0 1

Index Scan using idx_integrationaudit_transactiontype on integrationaudit (cost=0.41..161.32 rows=1 width=241) (actual time=0.122..0.122 rows=0 loops=1)

  • Index Cond: (transactiontype = ANY ('{ERX-CREATE,ERX-CANCEL}'::bpchar[]))
  • Filter: (((method)::bpchar = 'POST'::bpchar) AND ((responsecode)::bpchar = '200'::bpchar) AND ((transactiondate)::date >= '2018-10-31'::date) AND (lower(((attachment2)::bpchar)::text) ~~ '%1234%'::text))
  • Rows Removed by Filter: 49