explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Ko5

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 15.133 ↑ 1.0 1 1

Aggregate (cost=3,051.34..3,051.35 rows=1 width=64) (actual time=15.133..15.133 rows=1 loops=1)

2.          

CTE merchantsbysearchterm

3. 0.058 0.911 ↑ 1.0 23 1

Bitmap Heap Scan on merchants (cost=216.30..254.85 rows=24 width=16) (actual time=0.861..0.911 rows=23 loops=1)

  • Recheck Cond: ((search_vectors @@ plainto_tsquery('shoes'::text)) OR (search_vectors @@ phraseto_tsquery('shoes'::text)))
  • Heap Blocks: exact=57
4. 0.000 0.853 ↓ 0.0 0 1

BitmapOr (cost=216.30..216.30 rows=24 width=0) (actual time=0.853..0.853 rows=0 loops=1)

5. 0.462 0.462 ↓ 9.2 111 1

Bitmap Index Scan on search_vectors_idx (cost=0.00..108.14 rows=12 width=0) (actual time=0.461..0.462 rows=111 loops=1)

  • Index Cond: (search_vectors @@ plainto_tsquery('shoes'::text))
6. 0.391 0.391 ↓ 9.2 111 1

Bitmap Index Scan on search_vectors_idx (cost=0.00..108.14 rows=12 width=0) (actual time=0.391..0.391 rows=111 loops=1)

  • Index Cond: (search_vectors @@ phraseto_tsquery('shoes'::text))
7.          

CTE enabledmerchants

8. 0.615 8.988 ↑ 1.5 16 1

HashAggregate (cost=2,791.94..2,792.18 rows=24 width=16) (actual time=8.982..8.988 rows=16 loops=1)

  • Group Key: merchantsbysearchterm.merchant_id
9. 0.391 8.373 ↓ 3.6 3,707 1

Nested Loop (cost=0.42..2,789.37 rows=1,029 width=16) (actual time=0.885..8.373 rows=3,707 loops=1)

10. 0.921 0.921 ↑ 1.0 23 1

CTE Scan on merchantsbysearchterm (cost=0.00..0.48 rows=24 width=16) (actual time=0.862..0.921 rows=23 loops=1)

11. 7.061 7.061 ↓ 3.7 161 23

Index Scan using offers_offering_merchant_id_index on offers (cost=0.42..115.77 rows=43 width=16) (actual time=0.073..0.307 rows=161 loops=23)

  • Index Cond: (offering_merchant_id = merchantsbysearchterm.merchant_id)
  • Filter: (status = 'ON'::offer_status)
  • Rows Removed by Filter: 255
12. 0.004 15.107 ↓ 4.0 4 1

Subquery Scan on ranked_merchants (cost=4.28..4.30 rows=1 width=24) (actual time=15.102..15.107 rows=4 loops=1)

  • Filter: (ranked_merchants.rank = 1)
  • Rows Removed by Filter: 22
13. 0.010 15.103 ↓ 26.0 26 1

Sort (cost=4.28..4.29 rows=1 width=32) (actual time=15.101..15.103 rows=26 loops=1)

  • Sort Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false))
  • Sort Method: quicksort Memory: 27kB
14. 0.026 15.093 ↓ 26.0 26 1

WindowAgg (cost=4.00..4.27 rows=1 width=32) (actual time=15.075..15.093 rows=26 loops=1)

15. 0.017 15.067 ↓ 26.0 26 1

Sort (cost=4.00..4.00 rows=1 width=24) (actual time=15.065..15.067 rows=26 loops=1)

  • Sort Key: stores.owning_merchant_id, (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false))
  • Sort Method: quicksort Memory: 27kB
16. 0.014 15.050 ↓ 26.0 26 1

Group (cost=3.73..3.99 rows=1 width=24) (actual time=15.032..15.050 rows=26 loops=1)

  • Group Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false)), stores.owning_merchant_id
17. 0.034 15.036 ↓ 92.0 92 1

Sort (cost=3.73..3.73 rows=1 width=24) (actual time=15.030..15.036 rows=92 loops=1)

  • Sort Key: (_st_distance(stores.store_location, '0101000020E610000079211D1EC27E52C030F0DC7BB8604440'::geography, '0'::double precision, false)), stores.owning_merchant_id
  • Sort Method: quicksort Memory: 32kB
18. 0.323 15.002 ↓ 92.0 92 1

Hash Join (cost=2.89..3.72 rows=1 width=24) (actual time=14.921..15.002 rows=92 loops=1)

  • Hash Cond: (enabledmerchants.merchant_id = stores.owning_merchant_id)
19. 8.995 8.995 ↑ 1.5 16 1

CTE Scan on enabledmerchants (cost=0.00..0.48 rows=24 width=16) (actual time=8.983..8.995 rows=16 loops=1)

20. 0.407 5.684 ↓ 1,806.0 1,806 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 152kB
21. 5.277 5.277 ↓ 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.218..5.277 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.690 ms
Execution time : 15.241 ms