Обсуждение: query optimization
Hi, By running a relative complex query I get very slow response from postgresql. The query is and the explain analyze are: testsklad19=# explain analyze select N.IDS,N.IDS_NUM,N.IDS_SLU,N.IDS_SKLAD,SK.MNAME AS SKNAME,N.ZAPR,N.NOMNUM,NOM.NUM,N.NOMNAME,NOM.MNAME AS NOMNAME,N.PART,N.SERIENNUM,N.IZV,N.KOL,N.IDS_MED,N.MED,N.IDS_MED_MAIN,N.MED_MAIN,N.OTN_MED,N.CENA,N.VAL,N.KURS,N.CENADDS,N.DDS,N.CENA_LV,N.CENA_LVDDS,N.TOT,N.DTO,N.PTO,N.ORDER_NUM,N.IDS_DOC2,NOM.OSN_MED,NOM.DOP1_MED,NOM.DOP2_MED,NOM.OTN_DOP1_MED,NOM.OTN_DOP2_MED,N.DTON ,N.OFFIC,N.DATE_IZL,N.IZL,N.OTCH_CENA,N.CENAMITALV,N.CENATAKSILV,N.CENATRANSLV,N.CENA,N.CENAZASTRLV from A_SKLAD N left outer join A_LOCATION SK ON ( N.IDS_SKLAD=SK.IDS ) left outer join A_NOMEN NOM ON ( N.IDS_NUM = NOM.IDS ) where N.FID = 0 AND N.IDS_DOC = 'SOF_500' ORDER BY N.ORDER_NUM ; NOTICE: QUERY PLAN: Sort (cost=37.44..37.44 rows=1 width=1118) (actual time=12140.96..12141.02 rows=48 loops=1) -> Nested Loop (cost=0.00..37.43 rows=1 width=1118) (actual time=115.02..12138.57 rows=48 loops=1) -> Nested Loop (cost=0.00..33.42 rows=1 width=886) (actual time=0.58..20.77 rows=48 loops=1) -> Index Scan using i_sklad_ids_doc on a_sklad n (cost=0.00..28.58 rows=1 width=760) (actual time=0.36..8.59 rows=48 loops=1) -> Index Scan using a_location_pkey on a_location sk (cost=0.00..4.82 rows=1 width=126) (actual time=0.09..0.12 rows=1 loops=48) -> Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232) (actual time=0.03..165.45 rows=6702 loops=48) Total runtime: 12142.07 msec EXPLAIN How can I detect the problem? Exist any info or docs about how to interpred the analyze results? Many thanks in advance, Ivan.
> Sort (cost=37.44..37.44 rows=1 width=1118) (actual > time=12140.96..12141.02 rows=48 loops=1) > -> Nested Loop (cost=0.00..37.43 rows=1 width=1118) (actual > time=115.02..12138.57 rows=48 loops=1) > -> Nested Loop (cost=0.00..33.42 rows=1 width=886) (actual > time=0.58..20.77 rows=48 loops=1) > -> Index Scan using i_sklad_ids_doc on a_sklad n > (cost=0.00..28.58 rows=1 width=760) (actual time=0.36..8.59 rows=48 > loops=1) > -> Index Scan using a_location_pkey on a_location sk > (cost=0.00..4.82 rows=1 width=126) (actual time=0.09..0.12 rows=1 > loops=48) > -> Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232) > (actual time=0.03..165.45 rows=6702 loops=48) > Total runtime: 12142.07 msec It seems the query planner is completly wrong here, look on the line Seq Scan on a_nomen nom (cost=0.00..3.45 rows=45 width=232) (actual time=0.03..165.45 rows=6702 loops=48) This means the planner expects 45 return rows (guessed from statistics), but actually gets 6702 rows. Do "VACUUM ANALYZE a_nomen" and try your query again. If it fails: Do you have a unique index on a_nomen(ids)? Regards, Mario Weilguni > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
> > The query is and the explain analyze are: > > testsklad19=# explain analyze select > N.IDS,N.IDS_NUM,N.IDS_SLU,N.IDS_SKLAD,SK.MNAME AS > SKNAME,N.ZAPR,N.NOMNUM,NOM.NUM,N.NOMNAME,NOM.MNAME AS > NOMNAME,N.PART,N.SERIENNUM,N.IZV,N.KOL,N.IDS_MED,N.MED,N.IDS_MED_MAIN,N.MED_ MAIN,N.OTN_MED,N.CENA,N.VAL,N.KURS,N.CENADDS,N.DDS,N.CENA_LV,N.CENA_LVDDS,N. TOT,N.DTO,N.PTO,N.ORDER_NUM,N.IDS_DOC2,NOM.OSN_MED,NOM.DOP1_MED,NOM.DOP2_MED ,NOM.OTN_DOP1_MED,NOM.OTN_DOP2_MED,N.DTON > ,N.OFFIC,N.DATE_IZL,N.IZL,N.OTCH_CENA,N.CENAMITALV,N.CENATAKSILV,N.CENATRANS LV,N.CENA,N.CENAZASTRLV > from A_SKLAD N left outer join A_LOCATION SK ON ( N.IDS_SKLAD=SK.IDS ) > left outer join A_NOMEN NOM ON ( N.IDS_NUM = NOM.IDS ) where N.FID = 0 > AND N.IDS_DOC = 'SOF_500' ORDER BY N.ORDER_NUM ; > NOTICE: QUERY PLAN: > Well, can you tell us the index that are present on table A_SKLAD ? It seem that there are no index on the field FID, BTW if for you are always important only the line whit FID = 0 ( or other few values ) you can create a partial index: CREATE INDEX idx_partial ON a_sklad ( fid ) WHERE FID = 0; or if the values are more: CREATE INDEX idx_partial ON a_sklad ( fid ) WHERE FID IN ( val1, val2, .... ); Dont forget to do: 'vacuum analyze a_skland' after index creation. Good luck Gaetano