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