Re: bad plan
От | Richard Huxton |
---|---|
Тема | Re: bad plan |
Дата | |
Msg-id | 422DA6A5.2060908@archonet.com обсуждение исходный текст |
Ответ на | Re: bad plan (Gaetano Mendola <mendola@bigfoot.com>) |
Ответы |
Re: bad plan
(Gaetano Mendola <mendola@bigfoot.com>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: