Re: bad plan

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

Tom Lane wrote:
> Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
>
>>>Since your query is so simple, I'm guessing v_sc_user_request is a view.
>>>Can you provide the definition?
>
>
>>Of course:
>
>
> I don't think you've told us the whole truth about the v_sc_packages
> view. The definition as given doesn't work at all (it'll have
> duplicate column names), but more to the point, if it were that simple
> then the planner would fold it into the parent query. The subquery
> scan node indicates that folding did not occur. The most likely reason
> for that is that there's an ORDER BY in the view.

I didn't say the complete truth because the view definition is long so I just omitted
all fields.

explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
FROM v_sc_user_request
WHERE login = 'babinow1'
LIMIT 10 ;

these are the complete definitions of views involved in the query:

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
;

> Putting ORDER BYs in views that you intend to use as components of other
> views is a bad practice from a performance perspective...

Indeed when a view is involved in a join we do not put "order by" in it ( at
least this is what I try to do ), I have to say also that some time I see that replacing
the view with the tables that it represent the execution time is better
( I have an example to show you if you are interested in it ).

Regards
Gaetano Mendola

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

iD8DBQFCLkef7UpzwH2SGd4RAt90AJ9e3qUSx2fxiOO2aA30TbLsOdyV7ACfd0RY
+2A3U6dDfWw/H4eWcmI8mS0=
=t1AD
-----END PGP SIGNATURE-----