Bad Performance[2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,
running 7.4.x I still have problem
with the select but I do not find any solution apart to rise to 0.7 the
cpu_tuple_cost, I'm reposting it in the hope to discover a glitch in
the planner.

# explain analyze select * from v_sc_user_request where login = 'Zoneon';
QUERY PLAN
- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_sc_user_request (cost=1029.67..1029.68 rows=1 width=364) (actual time=319350.564..319352.632 rows=228 loops=1)
-> Sort (cost=1029.67..1029.68 rows=1 width=203) (actual time=319350.537..319350.683 rows=228 loops=1)
Sort Key: sr.id_sat_request
-> Nested Loop Left Join (cost=491.15..1029.66 rows=1 width=203) (actual time=897.252..319349.443 rows=228 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Nested Loop (cost=4.00..382.67 rows=1 width=195) (actual time=31.252..2635.751 rows=228 loops=1)
-> Hash Join (cost=4.00..379.59 rows=1 width=40) (actual time=31.174..578.979 rows=228 loops=1)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Index Scan using idx_sat_request_expired on sat_request sr (cost=0.00..360.02 rows=3112 width=28) (actual time=0.150..535.697 rows=7990 loops=1)
Index Cond: (expired = false)
Filter: (request_time > (now() - '1 mon'::interval))
-> Hash (cost=4.00..4.00 rows=2 width=16) (actual time=30.542..30.542 rows=0 loops=1)
-> Index Scan using user_login_login_key on user_login ul (cost=0.00..4.00 rows=2 width=16) (actual time=30.482..30.490 rows=1 loops=1)
Index Cond: ((login)::text = 'Zoneon'::text)
-> Index Scan using url_pkey on url u (cost=0.00..3.08 rows=1 width=163) (actual time=8.982..8.988 rows=1 loops=228)
Index Cond: ("outer".id_url = u.id_url)
-> Subquery Scan vsp (cost=487.15..642.42 rows=1298 width=12) (actual time=4.703..1384.172 rows=429 loops=228)
-> Hash Join (cost=487.15..641.12 rows=1298 width=128) (actual time=4.697..1382.081 rows=429 loops=228)
Hash Cond: ("outer".id_program = "inner".id_program)
-> Hash Join (cost=469.80..599.65 rows=1320 width=113) (actual time=0.755..30.305 rows=429 loops=228)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Hash Left Join (cost=13.86..79.54 rows=1479 width=101) (actual time=0.298..24.121 rows=1468 loops=228)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.48 rows=1479 width=101) (actual time=0.265..10.898 rows=1468 loops=228)
-> Hash (cost=11.10..11.10 rows=1104 width=4) (actual time=2.506..2.506 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..11.10 rows=1104 width=4) (actual time=0.018..1.433 rows=1096 loops=1)
-> Hash (cost=450.47..450.47 rows=2186 width=16) (actual time=92.435..92.435 rows=0 loops=1)
-> Seq Scan on sequences (cost=0.00..450.47 rows=2186 width=16) (actual time=0.044..91.641 rows=429 loops=1)
Filter: (estimated_start IS NOT NULL)
-> Hash (cost=17.20..17.20 rows=57 width=19) (actual time=0.383..0.383 rows=0 loops=1)
-> Seq Scan on programs (cost=0.00..17.20 rows=57 width=19) (actual time=0.024..0.323 rows=48 loops=1)
Filter: (id_program <> 0)
Total runtime: 319364.927 ms

# set cpu_tuple_cost = 0.7;

# explain analyze select * from v_sc_user_request where login = 'Zoneon';
QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_sc_user_request (cost=14708.99..14709.69 rows=1 width=364) (actual time=9956.650..9958.273 rows=228 loops=1)
-> Sort (cost=14708.99..14708.99 rows=1 width=203) (actual time=9956.635..9956.778 rows=228 loops=1)
Sort Key: sr.id_sat_request
-> Merge Left Join (cost=14701.75..14708.98 rows=1 width=203) (actual time=8138.468..9955.724 rows=228 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=6909.94..6909.95 rows=1 width=195) (actual time=5454.427..5454.760 rows=228 loops=1)
Sort Key: sr.id_package
-> Nested Loop (cost=4.70..6909.93 rows=1 width=195) (actual time=0.763..5453.236 rows=228 loops=1)
-> Hash Join (cost=4.70..6905.45 rows=1 width=40) (actual time=0.718..2325.661 rows=228 loops=1)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Index Scan using idx_sat_request_expired on sat_request sr (cost=0.00..6884.49 rows=3112 width=28) (actual time=0.090..2310.108 rows=7989 loops=1)
Index Cond: (expired = false)
Filter: (request_time > (now() - '1 mon'::interval))
-> Hash (cost=4.70..4.70 rows=2 width=16) (actual time=0.150..0.150 rows=0 loops=1)
-> Index Scan using user_login_login_key on user_login ul (cost=0.00..4.70 rows=2 width=16) (actual time=0.129..0.133 rows=1 loops=1)
Index Cond: ((login)::text = 'Zoneon'::text)
-> Index Scan using url_pkey on url u (cost=0.00..3.78 rows=1 width=163) (actual time=13.029..13.685 rows=1 loops=228)
Index Cond: ("outer".id_url = u.id_url)
-> Sort (cost=7791.81..7795.05 rows=1298 width=12) (actual time=2674.369..2674.791 rows=429 loops=1)
Sort Key: vsp.id_package
-> Subquery Scan vsp (cost=3026.61..7724.69 rows=1298 width=12) (actual time=177.979..2672.841 rows=429 loops=1)
-> Hash Join (cost=3026.61..6816.09 rows=1298 width=128) (actual time=177.969..2670.402 rows=429 loops=1)
Hash Cond: ("outer".id_program = "inner".id_program)
-> Hash Join (cost=2968.72..5826.77 rows=1320 width=113) (actual time=158.053..200.867 rows=429 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Hash Left Join (cost=785.56..2656.75 rows=1479 width=101) (actual time=3.127..40.350 rows=1468 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..1087.30 rows=1479 width=101) (actual time=0.039..24.680 rows=1468 loops=1)
-> Hash (cost=782.80..782.80 rows=1104 width=4) (actual time=2.622..2.622 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..782.80 rows=1104 width=4) (actual time=0.012..1.401 rows=1096 loops=1)
-> Hash (cost=2177.70..2177.70 rows=2186 width=16) (actual time=154.563..154.563 rows=0 loops=1)
-> Seq Scan on sequences (cost=0.00..2177.70 rows=2186 width=16) (actual time=0.012..153.654 rows=429 loops=1)
Filter: (estimated_start IS NOT NULL)
-> Hash (cost=57.74..57.74 rows=57 width=19) (actual time=0.289..0.289 rows=0 loops=1)
-> Seq Scan on programs (cost=0.00..57.74 rows=57 width=19) (actual time=0.022..0.224 rows=48 loops=1)
Filter: (id_program <> 0)
Total runtime: 9959.293 ms
(37 rows)

here the views definition:

CREATE OR REPLACE VIEW v_sc_user_request AS
SELECT
vsr.id_sat_request AS id_sat_request,
vsr.id_user AS id_user,
vsr.login AS login,
vsr.url AS url,
vsr.name AS name,
vsr.descr AS descr,
vsr.size AS size,
trunc(vsr.size/1024.0/1024.0,2) AS size_mb,
vsr.id_sat_request_status AS id_sat_request_status,
sp_lookup_key('sat_request_status', vsr.id_sat_request_status) AS request_status,
sp_lookup_descr('sat_request_status', vsr.id_sat_request_status) AS request_status_descr,
vsr.id_url_status AS id_url_status,
sp_lookup_key('url_status', vsr.id_url_status) AS url_status,
sp_lookup_descr('url_status', vsr.id_url_status) AS url_status_descr,
vsr.url_time_stamp AS url_time_stamp,
date_trunc('seconds',vsr.request_time) AS request_time_stamp,
vsr.id_package AS id_package,
COALESCE(date_trunc('seconds',vsp.estimated_start)::text,'NA') AS estimated_start

FROM
v_sat_request vsr LEFT OUTER JOIN v_sc_packages vsp USING ( id_package )
WHERE
vsr.request_time > now() - '1 month'::interval AND
vsr.expired = FALSE
ORDER BY id_sat_request DESC
;

CREATE OR REPLACE VIEW v_sat_request AS
SELECT
sr.id_user AS id_user,
ul.login AS login,
sr.id_sat_request AS id_sat_request,
u.id_url AS id_url,
u.url AS url,
u.name AS name,
u.descr AS descr,
u.size AS size,
u.storage AS storage,
sr.id_package AS id_package,
sr.id_sat_request_status AS id_sat_request_status,
sr.request_time AS request_time,
sr.work_time AS request_work_time,
u.id_url_status AS id_url_status,
u.time_stamp AS url_time_stamp,
sr.expired AS expired
FROM
sat_request sr,
url u,
user_login ul
WHERE
---------------- JOIN ---------------------
sr.id_url = u.id_url AND
sr.id_user = ul.id_user
-------------------------------------------
;

CREATE OR REPLACE VIEW v_sc_packages AS
SELECT

vpr.id_program AS id_program,
vpr.name AS program_name,

vpk.id_package AS id_package,
date_trunc('seconds', vs.estimated_start) AS estimated_start,

vpk.name AS package_name,
vpk.TYPE AS TYPE,
vpk.description AS description,
vpk.target AS target,
vpk.fec AS fec_alg,
vpk.output_group - vpk.input_group AS fec_redundancy,
vpk.priority AS priority,
vpk.updatable AS updatable,
vpk.auto_listen AS auto_listen,
vpk.start_file AS start_file,
vpk.view_target_group AS view_target_group,
vpk.target_group AS target_group

FROM
v_programs vpr,
v_packages vpk,
v_sequences vs

WHERE
------------ JOIN -------------
vpr.id_program = vs.id_program AND
vpk.id_package = vs.id_package AND

-------------------------------
vs.estimated_start IS NOT NULL
;

CREATE OR REPLACE VIEW v_programs AS
SELECT id_program AS id_program,
id_publisher AS id_publisher,
name AS name,
description AS description,
sp_lookup_key('program_type', id_program_type) AS TYPE,
sp_lookup_key('program_status', id_program_status) AS status,
last_position AS last_position
FROM programs
WHERE id_program<>0
;

CREATE OR REPLACE VIEW v_packages AS
SELECT p.id_package AS id_package,
p.id_publisher AS id_publisher,
p.name AS name,
p.information AS information,
p.description AS description,
sp_lookup_key('package_type', p.id_package_type)
AS TYPE,
sp_lookup_key('target', p.id_target)
AS target,
p.port AS port,
p.priority AS priority,
sp_lookup_key('fec', p.id_fec)
AS fec,
p.input_group AS input_group,
p.output_group AS output_group,
p.updatable AS updatable,
p.checksum AS checksum,
p.version AS version,
p.start_file AS start_file,
p.view_target_group AS view_target_group,
p.target_group AS target_group,
p.auto_listen AS auto_listen,
p.public_flag AS public_flag,
p.needed_version AS needed_version,
p.logic_version AS logic_version,
p.package_size AS package_size,
ps.id_drm_process AS id_drm_process,
ps.id_cas_service AS id_cas_service,
ps.id_cas_settings AS id_cas_settings,
ps.id_drm_service AS id_drm_service

FROM packages p LEFT OUTER JOIN package_security ps USING (id_package)
;

CREATE OR REPLACE VIEW v_sequences AS
SELECT id_package AS id_package,
id_program AS id_program,
internal_position AS internal_position,
estimated_start AS estimated_start
FROM sequences
;

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCNcAn7UpzwH2SGd4RAkBrAJ4+TFXKVggjNH2ddjezNt1GAGgSAQCfXGQt
BeEVkXECodZRCg395mAdaJE=
=UVGS
-----END PGP SIGNATURE-----