explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yrt1 : SLOW WITH INDEX ADDRESS

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 8,354.632 ↑ 1.0 12 1

Limit (cost=89,859.57..89,859.60 rows=12 width=426) (actual time=8,354.632..8,354.632 rows=12 loops=1)

2. 6.021 8,354.631 ↑ 5.2 12 1

Sort (cost=89,859.57..89,859.72 rows=63 width=426) (actual time=8,354.631..8,354.631 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.351 8,348.610 ↓ 26.6 1,678 1

Hash Left Join (cost=1,182.28..89,858.12 rows=63 width=426) (actual time=49.105..8,348.610 rows=1,678 loops=1)

  • Hash Cond: (prd_product.prdx_id = anon_1.prdx_id__product)
4. 4.772 147.226 ↓ 26.6 1,678 1

Nested Loop (cost=1,146.13..3,239.32 rows=63 width=418) (actual time=40.549..147.226 rows=1,678 loops=1)

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

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

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

Seq Scan on adrprd_addressofproduct (cost=0.00..1,661.94 rows=4,855 width=4) (actual time=0.014..14.404 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. 13.480 40.373 ↓ 6.8 42,292 1

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

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

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

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

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

10. 0.362 1.349 ↓ 5.8 1,538 1

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

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

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

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

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

13. 0.012 0.119 ↑ 1.1 48 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
14. 0.064 0.107 ↑ 1.1 48 1

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

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

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

16. 0.002 0.005 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=4) (actual time=0.005..0.005 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=418) (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.003 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
20. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on anon_1 (cost=29.65..33.65 rows=200 width=12) (actual time=0.003..0.003 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. 13.424 8,197.030 ↓ 0.0 0 1,678

Nested Loop Left Join (cost=30.94..1,374.30 rows=1 width=0) (actual time=4.885..4.885 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. 8.390 8,181.928 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=30.66..1,367.56 rows=1 width=10) (actual time=2.469..4.876 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 8,170.182 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=30.66..1,365.86 rows=1 width=10) (actual time=2.462..4.869 rows=1 loops=1,678)

27. 6.712 8,163.470 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=15.41..1,340.39 rows=1 width=13) (actual time=2.458..4.865 rows=1 loops=1,678)

28. 11.746 8,153.402 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=0.15..1,314.92 rows=1 width=12) (actual time=2.453..4.859 rows=1 loops=1,678)

29. 8,104.740 8,104.740 ↑ 1.0 1 1,678

Seq Scan on rit_rights (cost=0.00..1,301.15 rows=1 width=4) (actual time=2.424..4.830 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. 25.170 36.916 ↓ 0.0 0 1,678

Nested Loop (cost=0.15..13.76 rows=1 width=12) (actual time=0.022..0.022 rows=0 loops=1,678)

31. 11.746 11.746 ↓ 15.5 31 1,678

Seq Scan on psi_purchase_item (cost=0.00..1.39 rows=2 width=8) (actual time=0.003..0.007 rows=31 loops=1,678)

  • Filter: ((polymorphic)::text = ANY ('{purchase_item_of_salable_of_platform,purchase_item_of_subscription}'::text[]))
32. 0.000 0.000 ↓ 0.0 0 52,018

Index Only Scan using right_accesses_salable_of_platforms_purchased_items_table_pkey on right_accesses_salable_of_platforms_purchased_items_table right_accesses_salable_of_platforms_purchased_items_table_1 (cost=0.15..6.18 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=52,018)

  • Index Cond: ((psi_id = psi_purchase_item.psi_id) AND (rit_id = rit_rights.rit_id))
  • Heap Fetches: 0
33. 1.678 3.356 ↓ 0.0 0 1,678

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

  • Recheck Cond: (rit_rights.rit_id = rit_id__right)
34. 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)
35. 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)
36. 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)
37. 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)

38. 1.678 1.678 ↓ 0.0 0 1,678

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

39. 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..6.16 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,678)

  • Index Cond: (psi_id = psi_purchase_item.psi_id)
40. 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.54 rows=1 width=4) (never executed)

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