explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8tx6

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=534,704,291.95..534,759,705.74 rows=30,000 width=448) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Unique (cost=534,704,291.95..541,344,370.91 rows=3,594,816 width=448) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=534,704,291.95..535,053,769.79 rows=139,791,136 width=448) (actual rows= loops=)

  • Sort Key: (ROW(r.id, r2.id)), r.id, r.name, r.market_name, ra.address, ra.address_by_google, ((r.created_at)::date), r2.id, r2.name, r2.market_name, r2.retailer_address, r2.address_by_google, ((r2.created_at)::date), (similarity((r.name)::text, (r2.name)::text)), (similarity((r.market_name)::text, (r2.market_name)::text)), (similarity((ra.address)::text, (r2.retailer_address)::text)), (similarity((ra.address_by_google)::text, (r2.address_by_google)::text)), (st_distance_sphere(st_makepoint(ra.latitude, ra.longitude), st_makepoint(r2.latitude, r2.longitude)))
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=50,578.11..403,030,239.54 rows=139,791,136 width=448) (actual rows= loops=)

  • Join Filter: ((r.id <> r2.id) AND ((similarity((r.name)::text, (r2.name)::text) > '0.4'::double precision) OR (similarity((r.market_name)::text, (r2.market_name)::text) > '0.4'::double precision)) AND ((similarity((ra.address)::text, (r2.retailer_address)::text) > '0.4'::double precision) OR (similarity((ra.address_by_google)::text, (r2.address_by_google)::text) > '0.4'::double precision) OR (st_distance_sphere(st_makepoint(ra.latitude, ra.longitude), st_makepoint(r2.latitude, r2.longitude)) < '500'::double precision)))
5. 0.000 0.000 ↓ 0.0

Unique (cost=25,289.05..25,762.98 rows=18,957 width=220) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=25,289.05..25,336.45 rows=18,957 width=220) (actual rows= loops=)

  • Sort Key: r.id, r.name, r.market_name, r.created_at, r.district_id, ra.address, ra.address_by_google, ra.latitude, ra.longitude
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=20,068.81..21,933.12 rows=18,957 width=220) (actual rows= loops=)

  • Hash Cond: (ra.retailer_id = r.id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on retailer_addresses ra (cost=0.00..1,541.42 rows=36,842 width=168) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=19,833.86..19,833.86 rows=18,796 width=60) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,169.44..19,833.86 rows=18,796 width=60) (actual rows= loops=)

  • Hash Cond: (sales_orders.retailer_id = r.id)
11. 0.000 0.000 ↓ 0.0

Seq Scan on sales_orders (cost=0.00..17,615.05 rows=18,805 width=4) (actual rows= loops=)

  • Filter: ((sales_order_status_id = ANY ('{6,9}'::integer[])) AND (delivered_at >= '2020-05-01 00:00:00'::timestamp without time zone) AND (delivered_at <= '2020-05-31 00:00:00'::timestamp without time zone))
12. 0.000 0.000 ↓ 0.0

Hash (cost=1,700.86..1,700.86 rows=37,486 width=56) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on retailers r (cost=0.00..1,700.86 rows=37,486 width=56) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Materialize (cost=25,289.05..26,603.33 rows=18,957 width=216) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Subquery Scan on r2 (cost=25,289.05..25,952.55 rows=18,957 width=216) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Unique (cost=25,289.05..25,762.98 rows=18,957 width=220) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=25,289.05..25,336.45 rows=18,957 width=220) (actual rows= loops=)

  • Sort Key: r_1.id, r_1.name, r_1.market_name, r_1.created_at, r_1.district_id, ra_1.address, ra_1.address_by_google, ra_1.latitude, ra_1.longitude
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=20,068.81..21,933.12 rows=18,957 width=220) (actual rows= loops=)

  • Hash Cond: (ra_1.retailer_id = r_1.id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on retailer_addresses ra_1 (cost=0.00..1,541.42 rows=36,842 width=168) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=19,833.86..19,833.86 rows=18,796 width=60) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,169.44..19,833.86 rows=18,796 width=60) (actual rows= loops=)

  • Hash Cond: (sales_orders_1.retailer_id = r_1.id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on sales_orders sales_orders_1 (cost=0.00..17,615.05 rows=18,805 width=4) (actual rows= loops=)

  • Filter: ((sales_order_status_id = ANY ('{6,9}'::integer[])) AND (delivered_at >= '2020-05-01 00:00:00'::timestamp without time zone) AND (delivered_at <= '2020-05-31 00:00:00'::timestamp without time zone))
23. 0.000 0.000 ↓ 0.0

Hash (cost=1,700.86..1,700.86 rows=37,486 width=56) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on retailers r_1 (cost=0.00..1,700.86 rows=37,486 width=56) (actual rows= loops=)