explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uwVy : SLOW WITH INDEX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 7,504.876 ↑ 1.0 12 1

Limit (cost=92,068.85..92,068.88 rows=12 width=431) (actual time=7,504.874..7,504.876 rows=12 loops=1)

2. 4.601 7,504.876 ↑ 5.3 12 1

Sort (cost=92,068.85..92,069.01 rows=64 width=431) (actual time=7,504.874..7,504.876 rows=12 loops=1)

  • Sort Key: ((SubPlan 1)), (GREATEST(anon_1.created_at, prd_product.prdx_created_at))
  • Sort Method: top-N heapsort Memory: 33kB
3. 4.977 7,500.275 ↓ 26.2 1,678 1

Hash Left Join (cost=1,182.28..92,067.38 rows=64 width=431) (actual time=29.945..7,500.275 rows=1,678 loops=1)

  • Hash Cond: (prd_product.prdx_id = anon_1.prdx_id__product)
4. 0.000 123.842 ↓ 26.2 1,678 1

Nested Loop (cost=1,146.13..3,239.32 rows=64 width=423) (actual time=24.873..123.842 rows=1,678 loops=1)

  • Join Filter: (adrprd_addressofproduct.prdx_id = prd_product.prdx_id)
5. 38.711 76.686 ↓ 34.8 23,607 1

Hash Join (cost=1,145.84..2,863.12 rows=679 width=8) (actual time=24.783..76.686 rows=23,607 loops=1)

  • Hash Cond: (adrprd_addressofproduct.prdx_id = alias598.prdx_id)
6. 13.222 13.222 ↓ 4.9 23,773 1

Seq Scan on adrprd_addressofproduct (cost=0.00..1,661.94 rows=4,855 width=4) (actual time=0.010..13.222 rows=23,773 loops=1)

  • Filter: ((((adr_latitude - 36.4958995544576::double precision) * (adr_latitude - 36.9691569345554::double precision)) <= 0::double precision) AND (((adr_longitude - 2.72646789550777::double precision) * (adr_longitude - 3.47216491699214::double precision)) <= 0::double precision))
  • Rows Removed by Filter: 19925
7. 9.137 24.753 ↓ 6.8 42,292 1

Hash (cost=1,067.80..1,067.80 rows=6,243 width=4) (actual time=24.753..24.753 rows=42,292 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB
8. 11.887 15.616 ↓ 6.9 43,138 1

Hash Join (cost=53.46..1,067.80 rows=6,243 width=4) (actual time=1.014..15.616 rows=43,138 loops=1)

  • Hash Cond: (alias598.cty_id = alias1018.cty_id)
9. 2.729 2.729 ↑ 1.0 43,698 1

Seq Scan on adrprd_addressofproduct alias598 (cost=0.00..787.98 rows=43,698 width=8) (actual time=0.002..2.729 rows=43,698 loops=1)

10. 0.296 1.000 ↓ 5.8 1,538 1

Hash (cost=50.16..50.16 rows=264 width=4) (actual time=1.000..1.000 rows=1,538 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
11. 0.449 0.704 ↓ 5.8 1,538 1

Hash Join (cost=10.14..50.16 rows=264 width=4) (actual time=0.134..0.704 rows=1,538 loops=1)

  • Hash Cond: (alias1018.stt_id = alias1601.stt_id)
12. 0.131 0.131 ↑ 1.0 1,846 1

Seq Scan on cty_cities alias1018 (cost=0.00..30.46 rows=1,846 width=8) (actual time=0.001..0.131 rows=1,846 loops=1)

13. 0.005 0.124 ↑ 1.1 48 1

Hash (cost=9.51..9.51 rows=51 width=4) (actual time=0.124..0.124 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
14. 0.059 0.119 ↑ 1.1 48 1

Hash Join (cost=1.10..9.51 rows=51 width=4) (actual time=0.066..0.119 rows=48 loops=1)

  • Hash Cond: (alias1601.cnt_id = alias1085.cnt_id)
15. 0.056 0.056 ↑ 1.0 356 1

Seq Scan on stt_state alias1601 (cost=0.00..6.56 rows=356 width=8) (actual time=0.030..0.056 rows=356 loops=1)

16. 0.001 0.004 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on cnt_countries alias1085 (cost=0.00..1.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: ((cnt_name)::text = 'Algérie'::text)
  • Rows Removed by Filter: 6
18. 47.214 47.214 ↓ 0.0 0 23,607

Index Scan using prd_product_pkey on prd_product (cost=0.29..0.54 rows=1 width=423) (actual time=0.002..0.002 rows=0 loops=23,607)

  • Index Cond: (prdx_id = alias598.prdx_id)
  • Filter: (((polymorphic)::text = ANY ('{product_of_baytic,dad_deleted_product_of_baytic}'::text[])) AND (prd_status = 'published'::product_status))
  • Rows Removed by Filter: 1
19. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=33.65..33.65 rows=200 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
20. 0.000 0.002 ↓ 0.0 0 1

Subquery Scan on anon_1 (cost=29.65..33.65 rows=200 width=12) (actual time=0.002..0.002 rows=0 loops=1)

21. 0.002 0.002 ↓ 0.0 0 1

HashAggregate (cost=29.65..31.65 rows=200 width=12) (actual time=0.002..0.002 rows=0 loops=1)

22. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on pru_productup (cost=0.00..23.10 rows=1,310 width=12) (actual time=0.000..0.000 rows=0 loops=1)

23.          

SubPlan (for Hash Left Join)

24. 8.390 7,371.454 ↓ 0.0 0 1,678

Nested Loop Left Join (cost=61.80..1,387.35 rows=1 width=0) (actual time=4.393..4.393 rows=0 loops=1,678)

  • Filter: (ritusr_users_rights.ritusr_forced_access OR ritrle_roles_rights.ritrle_has_forced_access OR ((prc_purchase.prc_status = ANY ('{reserved,finished}'::prc_purchase_status[])) AND (pisu_purchase_item_of_subscription_1.usr_started_at <= to_date('2020-03-19 09:44:46'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND ((pisu_purchase_item_of_subscription_1.usr_started_at + ('1 mon'::interval * (pisu_purchase_item_of_subscription_1.sub_duration)::double precision)) >= to_date('2020-03-19 09:44:46'::text, 'YYYY-MM-DD HH24:MI:SS'::text))) OR (prc_purchase.prc_status = ANY ('{reserved,finished}'::prc_purchase_status[])))
  • Rows Removed by Filter: 1
25. 6.712 7,361.386 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=61.52..1,386.35 rows=1 width=10) (actual time=2.218..4.387 rows=1 loops=1,678)

  • Join Filter: (psi_purchase_item.prc_id__purchase = prc_purchase.prc_id)
  • Rows Removed by Join Filter: 31
26. 5.034 7,351.318 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=61.52..1,384.65 rows=1 width=10) (actual time=2.212..4.381 rows=1 loops=1,678)

27. 5.034 7,344.606 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=46.27..1,359.18 rows=1 width=13) (actual time=2.208..4.377 rows=1 loops=1,678)

28. 11.746 7,337.894 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=31.02..1,333.71 rows=1 width=12) (actual time=2.205..4.373 rows=1 loops=1,678)

29. 7,311.046 7,311.046 ↑ 1.0 1 1,678

Seq Scan on rit_rights (cost=0.00..1,301.15 rows=1 width=4) (actual time=2.188..4.357 rows=1 loops=1,678)

  • Filter: ((product_id = prd_product.prdx_id) AND ((rit_polymorphic)::text = 'right_by_access_product'::text))
  • Rows Removed by Filter: 42809
30. 8.388 15.102 ↓ 0.0 0 1,678

Hash Join (cost=31.02..32.54 rows=2 width=12) (actual time=0.009..0.009 rows=0 loops=1,678)

  • Hash Cond: (psi_purchase_item.psi_id = right_accesses_salable_of_platforms_purchased_items_table_1.psi_id)
31. 0.002 0.002 ↑ 31.0 1 1

Seq Scan on psi_purchase_item (cost=0.00..1.39 rows=31 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((polymorphic)::text = ANY ('{purchase_item_of_salable_of_platform,purchase_item_of_subscription}'::text[]))
32. 3.356 6.712 ↓ 0.0 0 1,678

Hash (cost=30.88..30.88 rows=11 width=8) (actual time=0.004..0.004 rows=0 loops=1,678)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
33. 1.678 3.356 ↓ 0.0 0 1,678

Bitmap Heap Scan on right_accesses_salable_of_platforms_purchased_items_table right_accesses_salable_of_platforms_purchased_items_table_1 (cost=20.21..30.88 rows=11 width=8) (actual time=0.002..0.002 rows=0 loops=1,678)

  • Recheck Cond: (rit_rights.rit_id = rit_id)
34. 1.678 1.678 ↓ 0.0 0 1,678

Bitmap Index Scan on right_accesses_salable_of_platforms_purchased_items_table_pkey (cost=0.00..20.21 rows=11 width=0) (actual time=0.001..0.001 rows=0 loops=1,678)

  • Index Cond: (rit_rights.rit_id = rit_id)
35. 0.000 1.678 ↓ 0.0 0 1,678

Bitmap Heap Scan on ritusr_users_rights (cost=15.25..25.40 rows=7 width=5) (actual time=0.001..0.001 rows=0 loops=1,678)

  • Recheck Cond: (rit_rights.rit_id = rit_id__right)
36. 1.678 1.678 ↓ 0.0 0 1,678

Bitmap Index Scan on ritusr_users_rights_pkey (cost=0.00..15.25 rows=7 width=0) (actual time=0.001..0.001 rows=0 loops=1,678)

  • Index Cond: (rit_rights.rit_id = rit_id__right)
37. 0.000 1.678 ↓ 0.0 0 1,678

Bitmap Heap Scan on ritrle_roles_rights (cost=15.25..25.40 rows=7 width=5) (actual time=0.001..0.001 rows=0 loops=1,678)

  • Recheck Cond: (rit_rights.rit_id = rit_id__right)
38. 1.678 1.678 ↓ 0.0 0 1,678

Bitmap Index Scan on ritrle_roles_rights_pkey (cost=0.00..15.25 rows=7 width=0) (actual time=0.001..0.001 rows=0 loops=1,678)

  • Index Cond: (rit_rights.rit_id = rit_id__right)
39. 3.356 3.356 ↑ 1.0 31 1,678

Seq Scan on prc_purchase (cost=0.00..1.31 rows=31 width=8) (actual time=0.001..0.002 rows=31 loops=1,678)

40. 1.678 1.678 ↓ 0.0 0 1,678

Nested Loop (cost=0.28..0.97 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,678)

41. 0.000 0.000 ↓ 0.0 0 1,678

Index Scan using pisu_purchase_item_of_subscription_pkey on pisu_purchase_item_of_subscription pisu_purchase_item_of_subscription_1 (cost=0.14..0.54 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,678)

  • Index Cond: (psi_id = psi_purchase_item.psi_id)
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using psi_purchase_item_pkey on psi_purchase_item psi_purchase_item_1 (cost=0.14..0.42 rows=1 width=4) (never executed)

  • Index Cond: (psi_id = pisu_purchase_item_of_subscription_1.psi_id)
  • Heap Fetches: 0"Total runtime: 7505.105 ms