explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l1aR : SLOW WITH INDEX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8,117.893 ↑ 1.0 12 1

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

2. 5.361 8,117.891 ↑ 5.2 12 1

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

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

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

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

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

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

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

Seq Scan on adrprd_addressofproduct (cost=0.00..1,661.94 rows=4,855 width=4) (actual time=0.011..16.346 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. 11.438 31.758 ↓ 6.8 42,292 1

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

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

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

  • Hash Cond: (alias598.cty_id = alias1018.cty_id)
9. 3.976 3.976 ↑ 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..3.976 rows=43,698 loops=1)

10. 0.275 0.993 ↓ 5.8 1,538 1

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

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

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

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

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

13. 0.010 0.100 ↑ 1.1 48 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
14. 0.055 0.090 ↑ 1.1 48 1

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

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

Seq Scan on stt_state alias1601 (cost=0.00..6.56 rows=356 width=8) (actual time=0.004..0.031 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=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.001 0.005 ↓ 0.0 0 1

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

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

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

21. 0.001 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.001 0.001 ↓ 0.0 0 1

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

23.          

SubPlan (for Hash Left Join)

24. 10.068 7,965.466 ↓ 0.0 0 1,678

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

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

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

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

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

28. 10.068 7,926.872 ↑ 1.0 1 1,678

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

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

Seq Scan on rit_rights (cost=0.00..1,301.15 rows=1 width=4) (actual time=2.360..4.697 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. 23.492 35.238 ↓ 0.0 0 1,678

Nested Loop (cost=0.15..13.76 rows=1 width=12) (actual time=0.021..0.021 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.002..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: 8118.146 ms