Re: bad plan - Mailing list pgsql-performance

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

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.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-----