From: Richard Huxton <dev@archonet.com>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: bad plan
Date: Tue, 08 Mar 2005 13:20:37 +0000
Message-ID: <422DA6A5.2060908@archonet.com> (raw)
In-Reply-To: <422D9904.5010904@bigfoot.com>
References: <422D7F23.7000109@bigfoot.com>
<422D8F0E.8000906@archonet.com>
<422D9904.5010904@bigfoot.com>
Gaetano Mendola wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Richard Huxton wrote:
>
>>Gaetano Mendola wrote:
>>
>>
>>>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 ;
>>
>>
>>> -> 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)
>>
>>
>>The problem to address is in this subquery. That's a total of 31 x
>>(1668.754 - 25.328) = 50seconds (about).
>>
>>Since your query is so simple, I'm guessing v_sc_user_request is a view.
>>Can you provide the definition?
>
>
> Of course:
>
>
>
> CREATE OR REPLACE VIEW v_sc_user_request AS
> SELECT
> *
> 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_sc_packages AS
> SELECT
> *
> 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_sat_request AS
> SELECT
> *
> FROM
> sat_request sr,
> url u,
> user_login ul
> WHERE
> ---------------- JOIN ---------------------
> sr.id_url = u.id_url AND
> sr.id_user = ul.id_user
> -------------------------------------------
> ;
OK, so looking at the original EXPLAIN the order of processing seems to be:
1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
This gives us 31 rows
2. The left-join from v_sat_request to v_sc_packages is processed (lines
5..6)
This involves the subquery scan on vsp (from line 16) where it seems to
think the best idea is a merge join of programs to sequences.
So - I think we need to look at the performance of your view
"v_sc_packages" and the views that it depends on. OK - can you reply to
this with just the definitions of v_sc_packages and what it depends on,
and we can have a look at that.
Do you need all these tables involved in this query? I don't think PG is
smart enough to completely discard a join if it's not needed by the
output. Thinking about it, I'm not sure you could safely.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, mendola@bigfoot.com
Subject: Re: bad plan
In-Reply-To: <422DA6A5.2060908@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox