From: Gaetano Mendola <mendola@bigfoot.com>
To: Richard Huxton <dev@archonet.com>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: bad plan
Date: Tue, 08 Mar 2005 13:22:28 +0100
Message-ID: <422D9904.5010904@bigfoot.com> (raw)
In-Reply-To: <422D8F0E.8000906@archonet.com>
References: <422D7F23.7000109@bigfoot.com>
<422D8F0E.8000906@archonet.com>
-----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
-------------------------------------------
;
that column expired was added since yesterday
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCLZkD7UpzwH2SGd4RAv8/AKCA5cNfu6vEKZ6m/ke1JsVRdsOTXQCbBMt4
ZPTFjwyb52CrFxdUTD6gejs=
=STzz
-----END PGP SIGNATURE-----
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: mendola@bigfoot.com, dev@archonet.com
Subject: Re: bad plan
In-Reply-To: <422D9904.5010904@bigfoot.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