explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zl3A

Settings
# exclusive inclusive rows x rows loops node
1. 1,346.132 172,182.787 ↓ 88.8 259,746 1

GroupAggregate (cost=4,268,980.26..4,269,148.49 rows=2,925 width=63) (actual time=169,269.391..172,182.787 rows=259,746 loops=1)

  • Output: shipper_pkey, division_pkey, reference_number, sum(CASE WHEN ((entry_source)::text ~~ '%_i201%'::text) THEN 1 ELSE 0 END), sum(CASE WHEN (((entry_source)::text ~~ '%_b.dat%'::text) OR ((entry_source)::text ~~ '%_c.dat%'::text)) THEN 1 ELSE 0 END), sum(CASE WHEN ((((entry_source)::text ~~ '%_i201%'::text) AND ((entry_source)::text ~~ '%_b.dat%'::text)) OR (((entry_source)::text ~~ '%_i201%'::text) AND ((entry_source)::text ~~ '%_c.dat%'::text))) THEN 1 ELSE
  • 0 END), (min(date_created))::date, count(*), count(DISTINCT reference_number)
  • Group Key: dfr.shipper_pkey, dfr.division_pkey, dfr.reference_number
  • Filter: (count(DISTINCT dfr.reference_number) = 1)
2. 4,947.312 170,836.655 ↓ 117.5 343,812 1

Sort (cost=4,268,980.26..4,268,987.58 rows=2,926 width=76) (actual time=169,269.279..170,836.655 rows=343,812 loops=1)

  • Output: shipper_pkey, division_pkey, reference_number, entry_source, date_created
  • Sort Key: dfr.shipper_pkey, dfr.division_pkey, dfr.reference_number
  • Sort Method: external merge Disk: 30320kB
3. 165,889.343 165,889.343 ↓ 117.5 343,812 1

Seq Scan on archive.datfile_request dfr (cost=0.00..4,268,811.80 rows=2,926 width=76) (actual time=20.960..165,889.343 rows=343,812 loops=1)

  • Output: shipper_pkey, division_pkey, reference_number, entry_source, date_created
  • Filter: (dfr.date_created >= (('now'::cstring)::date - '7 days'::interval day))
  • Rows Removed by Filter: 29163596