explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JhXN

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 43.861 ↑ 1.0 1 1

Aggregate (cost=27,088.05..27,088.06 rows=1 width=64) (actual time=43.861..43.861 rows=1 loops=1)

2.          

CTE enabledmerchants

3. 10.662 36.925 ↑ 4.8 337 1

HashAggregate (cost=27,029.67..27,045.86 rows=1,619 width=16) (actual time=36.865..36.925 rows=337 loops=1)

  • Group Key: offers.offering_merchant_id
4. 26.263 26.263 ↑ 1.0 69,287 1

Index Scan using offers_status_index on offers (cost=0.42..26,856.20 rows=69,389 width=16) (actual time=0.023..26.263 rows=69,287 loops=1)

  • Index Cond: (status = 'ON'::offer_status)
5. 0.027 43.810 ↓ 32.0 32 1

Subquery Scan on ranked_merchants (cost=42.16..42.18 rows=1 width=24) (actual time=43.766..43.810 rows=32 loops=1)

  • Filter: (ranked_merchants.rank = 1)
  • Rows Removed by Filter: 289
6. 0.087 43.783 ↓ 321.0 321 1

Sort (cost=42.16..42.17 rows=1 width=32) (actual time=43.765..43.783 rows=321 loops=1)

  • Sort Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false))
  • Sort Method: quicksort Memory: 50kB
7. 0.163 43.696 ↓ 321.0 321 1

WindowAgg (cost=41.88..42.15 rows=1 width=32) (actual time=43.521..43.696 rows=321 loops=1)

8. 0.123 43.533 ↓ 321.0 321 1

Sort (cost=41.88..41.88 rows=1 width=24) (actual time=43.516..43.533 rows=321 loops=1)

  • Sort Key: stores.owning_merchant_id, (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false))
  • Sort Method: quicksort Memory: 50kB
9. 0.137 43.410 ↓ 321.0 321 1

Group (cost=41.61..41.87 rows=1 width=24) (actual time=43.213..43.410 rows=321 loops=1)

  • Group Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false)), stores.owning_merchant_id
10. 0.358 43.273 ↓ 1,166.0 1,166 1

Sort (cost=41.61..41.61 rows=1 width=24) (actual time=43.212..43.273 rows=1,166 loops=1)

  • Sort Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false)), stores.owning_merchant_id
  • Sort Method: quicksort Memory: 140kB
11. 0.976 42.915 ↓ 1,166.0 1,166 1

Hash Join (cost=2.89..41.60 rows=1 width=24) (actual time=41.913..42.915 rows=1,166 loops=1)

  • Hash Cond: (enabledmerchants.offering_merchant_id = stores.owning_merchant_id)
12. 36.999 36.999 ↑ 4.8 337 1

CTE Scan on enabledmerchants (cost=0.00..32.38 rows=1,619 width=16) (actual time=36.867..36.999 rows=337 loops=1)

13. 0.346 4.940 ↓ 1,806.0 1,806 1

Hash (cost=2.88..2.88 rows=1 width=48) (actual time=4.940..4.940 rows=1,806 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 152kB
14. 4.594 4.594 ↓ 1,806.0 1,806 1

Index Scan using stores_store_location_gist on stores (cost=0.28..2.88 rows=1 width=48) (actual time=0.220..4.594 rows=1,806 loops=1)

  • Index Cond: (store_location && '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography)
  • Filter: (('0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography && _st_expand(store_location, '16093.4'::double precision)) AND _st_dwithin(store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '16093.4'::double precision, false))
  • Rows Removed by Filter: 564
Planning time : 0.487 ms
Execution time : 43.933 ms