explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJ1n

Settings
# exclusive inclusive rows x rows loops node
1. 2.632 11.445 ↑ 1.0 1 1

Aggregate (cost=23.70..23.71 rows=1 width=32) (actual time=11.445..11.445 rows=1 loops=1)

2.          

CTE merchantsinfo

3. 0.032 0.032 ↑ 1.0 7 1

Index Scan using merchants_pkey on merchants (cost=0.28..17.50 rows=7 width=129) (actual time=0.013..0.032 rows=7 loops=1)

  • Index Cond: (merchant_id = ANY ('{9e6f8f24-cae5-47d4-83c3-e14cc2607dea,5c5fb88b-c551-4c09-bbbb-8155b096bcc1,007ad1ac-b794-4c60-942a-a9765f49f639,f0d4cd05-4a63-40ee-9fdf-ab1c369b6ab0,004fdf3f-5bd8-4a6a-a902-58c81ce5ae50,440d5783-da0b-47e3-a757-c3e3ebf482f5,d3eee789-0c25-4916-b963-9060e412c631}'::uuid[]))
4. 0.383 8.813 ↓ 259.0 259 1

Nested Loop Semi Join (cost=3.31..5.93 rows=1 width=152) (actual time=5.291..8.813 rows=259 loops=1)

  • Join Filter: (stores.owning_merchant_id = merchantsinfo_1.merchant_id)
  • Rows Removed by Join Filter: 11,455
5. 1.053 6.624 ↓ 1,806.0 1,806 1

Nested Loop Left Join (cost=3.31..5.70 rows=1 width=152) (actual time=5.289..6.624 rows=1,806 loops=1)

6. 0.275 5.571 ↓ 1,806.0 1,806 1

Hash Right Join (cost=2.89..3.06 rows=1 width=152) (actual time=5.278..5.571 rows=1,806 loops=1)

  • Hash Cond: (merchantsinfo.merchant_id = stores.owning_merchant_id)
7. 0.037 0.037 ↑ 1.0 7 1

CTE Scan on merchantsinfo (cost=0.00..0.14 rows=7 width=64) (actual time=0.014..0.037 rows=7 loops=1)

8. 0.538 5.259 ↓ 1,806.0 1,806 1

Hash (cost=2.88..2.88 rows=1 width=104) (actual time=5.259..5.259 rows=1,806 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 265kB
9. 4.721 4.721 ↓ 1,806.0 1,806 1

Index Scan using stores_store_location_gist on stores (cost=0.28..2.88 rows=1 width=104) (actual time=0.166..4.721 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
10. 0.000 0.000 ↓ 0.0 0 1,806

Index Scan using offers_pkey on offers (cost=0.42..2.64 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=1,806)

  • Index Cond: (merchantsinfo.active_offer = offer_id)
11. 1.806 1.806 ↑ 1.2 6 1,806

CTE Scan on merchantsinfo merchantsinfo_1 (cost=0.00..0.14 rows=7 width=16) (actual time=0.000..0.001 rows=6 loops=1,806)

Planning time : 0.647 ms
Execution time : 11.497 ms