Hi all,
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT
name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-#FROM v_sc_user_request
empdb-#WHERE
empdb-# login = 'babinow1'
empdb-#LIMIT 10 ;
QUERY PLAN
--
Limit (cost=1716.38..1716.39 rows=1 width=232) (actual
time=52847.239..52847.322 rows=10 loops=1)
- Subquery Scan v_sc_user_request (cost=1716.38..1716.39 rows=1
width=232) (actual time=52847.234..52847.301 rows=10 loops=1)
- Sort (cost=1716.38..1716.39 rows=1 width=201) (actual
time=52847.219..52847.227 rows=10 loops=1)
Sort Key: sr.id_sat_request
- Nested Loop Left Join (cost=1478.82..1716.37 rows=1
width=201) (actual time=3254.483..52847.064 rows=31 loops=1)
Join Filter: (outer.id_package = inner.id_package)
- Nested Loop (cost=493.09..691.55 rows=1 width=193)
(actual time=347.665..940.582 rows=31 loops=1)
- Nested Loop (cost=493.09..688.49 rows=1
width=40) (actual time=331.446..505.628 rows=31 loops=1)
Join Filter: (inner.id_user =
outer.id_user)
- Index Scan using user_login_login_key on
user_login ul (cost=0.00..4.00 rows=2 width=16) (actual time=12.065..12.071
rows=1 loops=1)
Index Cond: ((login)::text =
'babinow1'::text)
- Materialize (cost=493.09..531.37
rows=7656 width=28) (actual time=167.654..481.813 rows=8363 loops=1)
- Seq Scan on sat_request sr
(cost=0.00..493.09 rows=7656 width=28) (actual time=167.644..467.344 rows=8363
loops=1)
Filter: (request_time (now() -
'1 mon'::interval))
- Index Scan using url_pkey on url u
(cost=0.00..3.05 rows=1 width=161) (actual time=13.994..14.000 rows=1 loops=31)
Index Cond: (outer.id_url = u.id_url)
- Subquery Scan vsp (cost=985.73..1016.53 rows=1103
width=12) (actual time=25.328..1668.754 rows=493 loops=31)
- Merge Join (cost=985.73..1011.01 rows=1103
width=130) (actual time=25.321..1666.666 rows=493 loops=31)
Merge Cond: (outer.id_program =
inner.id_program)
- Sort (cost=20.74..20.97 rows=93 width=19)
(actual time=0.385..0.431 rows=47 loops=31)
Sort Key: programs.id_program
- Seq Scan on programs
(cost=0.00..17.70 rows=93 width=19) (actual time=0.022..11.709 rows=48 loops=1)
Filter: (id_program 0)
- Sort (cost=964.99..967.75 rows=1102
width=115) (actual time=14.592..15.218 rows=493 loops=31)
Sort Key: sequences.id_program
- Merge Join (cost=696.16..909.31
rows=1102 width=115) (actual time=79.717..451.495 rows=493 loops=1)
Merge Cond: (outer.id_package =
inner.id_package)
- Merge Left Join
(cost=0.00..186.59 rows=1229 width=103) (actual time=0.101..366.854 rows=1247
loops=1)
Merge Cond:
(outer.id_package = inner.id_package)
- Index Scan using
packages_pkey on packages p (cost=0.00..131.04 rows=1229 width=103) (actual
time=0.048..163.503 rows=1247 loops=1)
- Index Scan using
package_security_id_package_key on package_security ps (cost=0.00..46.83
rows=855 width=4) (actual time=0.022..178.599 rows=879 loops=1)
- Sort (cost=696.16..705.69
rows=3812 width=16) (actual time=79.582..79.968 rows=493 loops=1)
Sort Key:
sequences.id_package
- Seq Scan on sequences
(cost=0.00..469.42 rows=3812 width=16) (actual time=0.012..78.863 rows=493
loops=1)
Filter:
(estimated_start IS NOT NULL)
Total runtime: 52878.516 ms
(36 rows)
Disabling the nestloop then the execution time become more affordable:
empdb=# set enable_nestloop = false;
SET
empdb=# explain analyze SELECT
