explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n9tM

Settings
# exclusive inclusive rows x rows loops node
1. 2.029 16.939 ↑ 1.0 1 1

Aggregate (cost=24.87..24.88 rows=1 width=32) (actual time=16.939..16.939 rows=1 loops=1)

2.          

CTE merchantsinfo

3. 0.026 0.026 ↑ 1.0 7 1

Index Scan using merchants_pkey on merchants (cost=0.28..17.50 rows=7 width=113) (actual time=0.007..0.026 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.387 14.910 ↓ 161.0 161 1

Nested Loop Semi Join (cost=0.70..7.10 rows=1 width=152) (actual time=0.470..14.910 rows=161 loops=1)

  • Join Filter: (stores.owning_merchant_id = merchantsinfo_1.merchant_id)
  • Rows Removed by Join Filter: 4,343
5. 0.538 13.799 ↓ 724.0 724 1

Nested Loop Left Join (cost=0.70..6.87 rows=1 width=152) (actual time=0.319..13.799 rows=724 loops=1)

  • Join Filter: (stores.owning_merchant_id = merchantsinfo.merchant_id)
  • Rows Removed by Join Filter: 4,907
6. 0.095 12.537 ↓ 724.0 724 1

Nested Loop (cost=0.70..6.64 rows=1 width=120) (actual time=0.288..12.537 rows=724 loops=1)

7. 5.218 5.218 ↓ 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.142..5.218 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
8. 7.224 7.224 ↓ 0.0 0 1,806

Index Scan using offers_offering_store_id_index on offers (cost=0.42..3.76 rows=1 width=32) (actual time=0.003..0.004 rows=0 loops=1,806)

  • Index Cond: (offering_store_id = stores.store_id)
  • Filter: (status = 'ON'::offer_status)
  • Rows Removed by Filter: 1
9. 0.724 0.724 ↑ 1.0 7 724

CTE Scan on merchantsinfo (cost=0.00..0.14 rows=7 width=48) (actual time=0.000..0.001 rows=7 loops=724)

10. 0.724 0.724 ↑ 1.2 6 724

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

Planning time : 0.706 ms
Execution time : 16.998 ms