explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IRF3 : QS

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 11,142.889 ↑ 1.0 12 1

Limit (cost=20,000,147,070.49..20,000,147,070.52 rows=12 width=423) (actual time=11,142.888..11,142.889 rows=12 loops=1)

2. 4.359 11,142.888 ↑ 5.3 12 1

Sort (cost=20,000,147,070.49..20,000,147,070.65 rows=64 width=423) (actual time=11,142.888..11,142.888 rows=12 loops=1)

  • Sort Key: ((SubPlan 1))
  • Sort Method: top-N heapsort Memory: 38kB
3. 11.765 11,138.529 ↓ 26.2 1,678 1

Hash Join (cost=20,000,003,171.58..20,000,147,069.02 rows=64 width=423) (actual time=58.058..11,138.529 rows=1,678 loops=1)

  • Hash Cond: (prd_product.prdx_id = alias1613.prdx_id)
4. 3.210 52.700 ↓ 3.8 1,678 1

Merge Join (cost=10,000,001,959.48..10,000,012,746.71 rows=446 width=427) (actual time=25.280..52.700 rows=1,678 loops=1)

  • Merge Cond: (prd_product.prdx_id = adrprd_addressofproduct.prdx_id)
5. 21.608 21.608 ↑ 1.0 3,975 1

Index Scan using prd_product_pkey on prd_product (cost=0.29..10,749.26 rows=4,015 width=423) (actual time=0.014..21.608 rows=3,975 loops=1)

  • Filter: (((polymorphic)::text = ANY ('{product_of_baytic,dad_deleted_product_of_baytic}'::text[])) AND (prd_status = 'published'::product_status))
  • Rows Removed by Filter: 38787
6. 16.147 27.882 ↓ 4.9 23,611 1

Sort (cost=10,000,001,959.19..10,000,001,971.33 rows=4,855 width=4) (actual time=25.259..27.882 rows=23,611 loops=1)

  • Sort Key: adrprd_addressofproduct.prdx_id
  • Sort Method: external sort Disk: 424kB
7. 11.735 11.735 ↓ 4.9 23,773 1

Seq Scan on adrprd_addressofproduct (cost=10,000,000,000.00..10,000,001,661.94 rows=4,855 width=4) (actual time=0.007..11.735 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
8. 9.547 26.112 ↓ 6.8 42,292 1

Hash (cost=10,000,001,134.06..10,000,001,134.06 rows=6,243 width=4) (actual time=26.112..26.112 rows=42,292 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB
9. 12.780 16.565 ↓ 6.9 43,138 1

Hash Join (cost=10,000,000,119.73..10,000,001,134.06 rows=6,243 width=4) (actual time=1.133..16.565 rows=43,138 loops=1)

  • Hash Cond: (alias1613.cty_id = alias366.cty_id)
10. 2.658 2.658 ↑ 1.0 43,698 1

Seq Scan on adrprd_addressofproduct alias1613 (cost=10,000,000,000.00..10,000,000,787.98 rows=43,698 width=8) (actual time=0.002..2.658 rows=43,698 loops=1)

11. 0.260 1.127 ↓ 5.8 1,538 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
12. 0.474 0.867 ↓ 5.8 1,538 1

Hash Join (cost=32.17..116.43 rows=264 width=4) (actual time=0.067..0.867 rows=1,538 loops=1)

  • Hash Cond: (alias366.stt_id = alias983.stt_id)
13. 0.338 0.338 ↑ 1.0 1,846 1

Index Scan using cty_cities_pkey on cty_cities alias366 (cost=0.28..74.97 rows=1,846 width=8) (actual time=0.008..0.338 rows=1,846 loops=1)

14. 0.008 0.055 ↑ 1.1 48 1

Hash (cost=31.25..31.25 rows=51 width=4) (actual time=0.055..0.055 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
15. 0.012 0.047 ↑ 1.1 48 1

Nested Loop (cost=19.09..31.25 rows=51 width=4) (actual time=0.035..0.047 rows=48 loops=1)

16. 0.008 0.008 ↑ 1.0 1 1

Index Scan using cnt_countries_cnt_name_key on cnt_countries alias957 (cost=0.13..8.15 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((cnt_name)::text = 'Algérie'::text)
17. 0.007 0.027 ↑ 1.1 48 1

Bitmap Heap Scan on stt_state alias983 (cost=18.96..22.59 rows=51 width=8) (actual time=0.025..0.027 rows=48 loops=1)

  • Recheck Cond: (cnt_id = alias957.cnt_id)
18. 0.020 0.020 ↑ 1.1 48 1

Bitmap Index Scan on _unique_state_country_id (cost=0.00..18.94 rows=51 width=0) (actual time=0.020..0.020 rows=48 loops=1)

  • Index Cond: (cnt_id = alias957.cnt_id)
19.          

SubPlan (for Hash Join)

20. 11.746 11,047.952 ↓ 0.0 0 1,678

Nested Loop Left Join (cost=51.56..2,079.81 rows=1 width=0) (actual time=6.584..6.584 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 11:09:55'::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 11:09:55'::text, 'YYYY-MM-DD HH24:MI:SS'::text))) OR (prc_purchase.prc_status = ANY ('{reserved,finished}'::prc_purchase_status[])))
  • Rows Removed by Filter: 1
21. 6.712 11,036.206 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=51.42..2,079.24 rows=1 width=18) (actual time=3.309..6.577 rows=1 loops=1,678)

22. 5.034 11,027.816 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=36.16..2,053.77 rows=1 width=21) (actual time=3.305..6.572 rows=1 loops=1,678)

23. 1.678 11,019.426 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=20.91..2,028.30 rows=1 width=20) (actual time=3.301..6.567 rows=1 loops=1,678)

24. 3.356 11,016.070 ↑ 1.0 1 1,678

Nested Loop Left Join (cost=20.64..2,027.32 rows=1 width=12) (actual time=3.298..6.565 rows=1 loops=1,678)

25. 11,000.968 11,000.968 ↑ 1.0 1 1,678

Index Scan using rit_rights_pkey on rit_rights (cost=0.29..1,994.49 rows=1 width=4) (actual time=3.289..6.556 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
26. 6.712 11.746 ↓ 0.0 0 1,678

Nested Loop (cost=20.35..32.81 rows=2 width=12) (actual time=0.007..0.007 rows=0 loops=1,678)

27. 1.678 5.034 ↓ 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.003..0.003 rows=0 loops=1,678)

  • Recheck Cond: (rit_rights.rit_id = rit_id)
28. 3.356 3.356 ↓ 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.002..0.002 rows=0 loops=1,678)

  • Index Cond: (rit_rights.rit_id = rit_id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using psi_purchase_item_pkey on psi_purchase_item (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (psi_id = right_accesses_salable_of_platforms_purchased_items_table_1.psi_id)
  • Filter: ((polymorphic)::text = ANY ('{purchase_item_of_salable_of_platform,purchase_item_of_subscription}'::text[]))
30. 0.000 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)

31. 1.678 1.678 ↓ 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.001..0.001 rows=0 loops=1,678)

  • Index Cond: (psi_id = psi_purchase_item.psi_id)
32. 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
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. 0.000 0.000 ↓ 0.0 0 1,678

Index Scan using prc_purchase_prc_id_prc_currency_key on prc_purchase (cost=0.14..0.54 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,678)

  • Index Cond: (psi_purchase_item.prc_id__purchase = prc_id)"Total runtime: 11143.148 ms