explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wDCq

Settings
# exclusive inclusive rows x rows loops node
1. 0.095 14.530 ↑ 1.0 1 1

Aggregate (cost=61,924.90..61,924.91 rows=1 width=64) (actual time=14.530..14.530 rows=1 loops=1)

2.          

CTE activemerchants

3. 5.236 5.236 ↑ 23.8 335 1

Seq Scan on merchants (cost=0.00..3,328.23 rows=7,983 width=32) (actual time=0.010..5.236 rows=335 loops=1)

  • Filter: (active_offer IS NOT NULL)
  • Rows Removed by Filter: 7,688
4.          

CTE activeoffers

5. 0.092 1.232 ↑ 103.6 335 1

Nested Loop (cost=180.04..689.72 rows=34,694 width=32) (actual time=0.122..1.232 rows=335 loops=1)

6. 0.106 0.135 ↓ 1.7 335 1

HashAggregate (cost=179.62..181.62 rows=200 width=16) (actual time=0.097..0.135 rows=335 loops=1)

  • Group Key: activemerchants.active_offer
7. 0.029 0.029 ↑ 23.8 335 1

CTE Scan on activemerchants (cost=0.00..159.66 rows=7,983 width=16) (actual time=0.000..0.029 rows=335 loops=1)

8. 1.005 1.005 ↑ 1.0 1 335

Index Scan using offers_pkey on offers (cost=0.42..2.54 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=335)

  • Index Cond: (offer_id = activemerchants.active_offer)
  • Filter: (status = 'ON'::offer_status)
9.          

CTE enabledmerchants

10. 0.117 7.031 ↑ 4,133.8 335 1

Merge Join (cost=3,987.29..24,799.37 rows=1,384,811 width=16) (actual time=6.856..7.031 rows=335 loops=1)

  • Merge Cond: (activemerchants_1.merchant_id = activeoffers.offering_merchant_id)
11. 0.117 5.462 ↑ 23.8 335 1

Sort (cost=677.07..697.02 rows=7,983 width=16) (actual time=5.431..5.462 rows=335 loops=1)

  • Sort Key: activemerchants_1.merchant_id
  • Sort Method: quicksort Memory: 40kB
12. 5.345 5.345 ↑ 23.8 335 1

CTE Scan on activemerchants activemerchants_1 (cost=0.00..159.66 rows=7,983 width=16) (actual time=0.012..5.345 rows=335 loops=1)

13. 0.111 1.452 ↑ 103.6 335 1

Sort (cost=3,310.22..3,396.96 rows=34,694 width=16) (actual time=1.422..1.452 rows=335 loops=1)

  • Sort Key: activeoffers.offering_merchant_id
  • Sort Method: quicksort Memory: 40kB
14. 1.341 1.341 ↑ 103.6 335 1

CTE Scan on activeoffers (cost=0.00..693.88 rows=34,694 width=16) (actual time=0.123..1.341 rows=335 loops=1)

15. 0.047 14.435 ↓ 32.0 32 1

Subquery Scan on ranked_merchants (cost=33,107.55..33,107.57 rows=1 width=24) (actual time=14.362..14.435 rows=32 loops=1)

  • Filter: (ranked_merchants.rank = 1)
  • Rows Removed by Filter: 289
16. 0.098 14.388 ↓ 321.0 321 1

Sort (cost=33,107.55..33,107.55 rows=1 width=32) (actual time=14.361..14.388 rows=321 loops=1)

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

WindowAgg (cost=33,107.27..33,107.54 rows=1 width=32) (actual time=14.096..14.290 rows=321 loops=1)

18. 0.197 14.107 ↓ 321.0 321 1

Sort (cost=33,107.27..33,107.27 rows=1 width=24) (actual time=14.088..14.107 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
19. 0.215 13.910 ↓ 321.0 321 1

Group (cost=33,101.88..33,107.26 rows=1 width=24) (actual time=13.615..13.910 rows=321 loops=1)

  • Group Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false)), stores.owning_merchant_id
20. 0.405 13.695 ↓ 1.7 1,166 1

Sort (cost=33,101.88..33,103.59 rows=683 width=24) (actual time=13.612..13.695 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
21. 1.112 13.290 ↓ 1.7 1,166 1

Hash Join (cost=2.89..33,069.73 rows=683 width=24) (actual time=12.105..13.290 rows=1,166 loops=1)

  • Hash Cond: (enabledmerchants.merchant_id = stores.owning_merchant_id)
22. 7.104 7.104 ↑ 4,133.8 335 1

CTE Scan on enabledmerchants (cost=0.00..27,696.22 rows=1,384,811 width=16) (actual time=6.857..7.104 rows=335 loops=1)

23. 0.383 5.074 ↓ 1,806.0 1,806 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 152kB
24. 4.691 4.691 ↓ 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.203..4.691 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.606 ms
Execution time : 14.681 ms