X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7EB55329EB0 for ; Wed, 8 Sep 2004 14:50:01 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76950-10 for ; Wed, 8 Sep 2004 13:49:51 +0000 (GMT) Received: from mercure.sigma.fr (mercure-1.sigma.fr [195.25.81.8]) by svr1.postgresql.org (Postfix) with ESMTP id 5DDB2329EA7 for ; Wed, 8 Sep 2004 14:49:52 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by mercure.sigma.fr (Postfix) with ESMTP id 96EF679F1 for ; Wed, 8 Sep 2004 15:49:50 +0200 (CEST) Received: from mercure.sigma.fr ([127.0.0.1]) by localhost (mercure-1 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04615-01 for ; Wed, 8 Sep 2004 15:49:50 +0200 (CEST) Received: from marc.sigma.fr (unknown [89.195.0.5]) by mercure.sigma.fr (Postfix) with ESMTP id 69D3679E5 for ; Wed, 8 Sep 2004 15:49:50 +0200 (CEST) From: Marc Cousin Organization: Sigma Informatique Subject: Problem with large query Date: Wed, 8 Sep 2004 15:49:49 +0200 User-Agent: KMail/1.7 MIME-Version: 1.0 Content-Disposition: inline To: pgsql-performance@postgresql.org Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message-Id: <200409081549.49537.mcousin@sigma.fr> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at sigma.fr X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/48 X-Sequence-Number: 8176 Hi. I hope I'm not asking a too trivial question here... I'm having trouble with a (quite big) query, and can't find a way to make i= t=20 faster. Here is the information : Tables : =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D sces_vte -> 2753539 rows sces_art -> 602327 sces_fsf -> 8126 sces_frc -> 7763 sces_tps -> 38 sces_gtr -> 35 Query : =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D SELECT sces_gtr_art.gtr_cod, sces_gtr_art.gtr_lib, sces_frc_art.fou_cod, sces_frc_art.fou_lib, sces_tps.tps_annee_mois, TO_NUMBER('200401','999999'), TO_NUMBER('200405','999999'), sces_tps.tps_libc, sum(sces_vte.vte_mnt), sum(sces_vte.vte_qte), sum(sces_vte.vte_ton), sces_famille.fsf_codfam, sces_famille.fsf_lib, sces_s_famille.fsf_codsfm, sces_s_famille.fsf_lib FROM sces_vte, sces_art, sces_fsf sces_famille, sces_fsf sces_s_famille, sces_frc sces_frc_art, sces_tps, sces_gtr sces_gtr_art WHERE ( sces_famille.fsf_codfam=3Dsces_s_famille.fsf_codfam ) AND ( sces_famille.fsf_codseg=3D 0 and sces_famille.fsf_codsfm =3D 0 ) AND ( sces_vte.tps_annee_mois=3Dsces_tps.tps_annee_mois ) AND ( sces_vte.art_cod=3Dsces_art.art_cod and=20 sces_vte.dos_cod=3Dsces_art.dos_cod ) AND ( sces_gtr_art.gtr_cod=3Dsces_frc_art.gtr_cod ) AND ( sces_frc_art.gtr_cod=3Dsces_art.gtr_cod and=20 sces_frc_art.fou_cod=3Dsces_art.fou_cod ) AND ( sces_s_famille.fsf_codfam=3Dsces_art.fsf_codfam and=20 sces_s_famille.fsf_codsfm=3Dsces_art.fsf_codsfm ) AND ( sces_s_famille.fsf_codseg =3D 0 ) AND ( ( ( ( sces_tps.tps_annee_mois ) >=3D ( TO_NUMBER('200401','999999') ) and= =20 ( sces_tps.tps_annee_mois ) <=3D ( TO_NUMBER('200405','999999') ) ) OR ( ( sces_tps.tps_annee_mois ) >=3D ( TO_NUMBER('200401','999999') )-100 and= =20 ( sces_tps.tps_annee_mois ) <=3D ( TO_NUMBER('200405','999999') )-100 ) ) AND ( sces_gtr_art.gtr_cod in (2)) ) GROUP BY sces_gtr_art.gtr_cod, sces_gtr_art.gtr_lib, sces_frc_art.fou_cod, sces_frc_art.fou_lib, sces_tps.tps_annee_mois, TO_NUMBER('200401','999999'), TO_NUMBER('200405','999999'), sces_tps.tps_libc, sces_famille.fsf_codfam, sces_famille.fsf_lib, sces_s_famille.fsf_codsfm, sces_s_famille.fsf_lib Explain Analyze Plan :=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D GroupAggregate (cost=3D27161.91..27938.72 rows=3D16354 width=3D280) (actu= al time=3D484509.210..544436.148 rows=3D4115 loops=3D1) -> Sort (cost=3D27161.91..27202.79 rows=3D16354 width=3D280) (actual t= ime=3D484496.188..485334.151 rows=3D799758 loops=3D1) Sort Key: sces_gtr_art.gtr_cod, sces_gtr_art.gtr_lib, sces_frc_art= .fou_cod, sces_frc_art.fou_lib, sces_tps.tps_annee_mois, 200401::numeric, 2= 00405::numeric, sces_tps.tps_libc, sces_famille.fsf_codfam, sces_famille.fs= f_lib, sces_s_famille.fsf_codsfm, sces_s_famille.fsf_lib -> Merge Join (cost=3D25727.79..26017.34 rows=3D16354 width=3D28= 0) (actual time=3D58945.821..69321.146 rows=3D799758 loops=3D1) Merge Cond: (("outer".fsf_codfam =3D "inner".fsf_codfam) AND= ("outer".fsf_codsfm =3D "inner".fsf_codsfm)) -> Sort (cost=3D301.36..304.60 rows=3D1298 width=3D83) (ac= tual time=3D27.926..28.256 rows=3D332 loops=3D1) Sort Key: sces_s_famille.fsf_codfam, sces_s_famille.fs= f_codsfm -> Seq Scan on sces_fsf sces_s_famille (cost=3D0.00.= .234.24 rows=3D1298 width=3D83) (actual time=3D0.042..19.124 rows=3D1341 lo= ops=3D1) Filter: (fsf_codseg =3D 0::numeric) -> Sort (cost=3D25426.43..25448.05 rows=3D8646 width=3D225= ) (actual time=3D58917.106..59693.810 rows=3D799758 loops=3D1) Sort Key: sces_art.fsf_codfam, sces_art.fsf_codsfm -> Merge Join (cost=3D24726.32..24861.08 rows=3D8646= width=3D225) (actual time=3D19036.709..29404.943 rows=3D799758 loops=3D1) Merge Cond: ("outer".tps_annee_mois =3D "inner".= tps_annee_mois) -> Sort (cost=3D2.49..2.53 rows=3D17 width=3D2= 3) (actual time=3D0.401..0.428 rows=3D20 loops=3D1) Sort Key: sces_tps.tps_annee_mois -> Seq Scan on sces_tps (cost=3D0.00..2.= 14 rows=3D17 width=3D23) (actual time=3D0.068..0.333 rows=3D20 loops=3D1) Filter: (((tps_annee_mois >=3D 20030= 1::numeric) OR (tps_annee_mois >=3D 200401::numeric)) AND ((tps_annee_mois = <=3D 200305::numeric) OR (tps_annee_mois >=3D 200401::numeric)) AND ((tps_a= nnee_mois >=3D 200301::numeric) OR (tps_annee_mois <=3D 200405::numeric)) A= ND ((tps_annee_mois <=3D 200305::numeric) OR (tps_annee_mois <=3D 200405::n= umeric))) -> Sort (cost=3D24723.83..24747.97 rows=3D9656= width=3D214) (actual time=3D19036.223..19917.214 rows=3D799757 loops=3D1) Sort Key: sces_vte.tps_annee_mois -> Nested Loop (cost=3D21825.09..24084.7= 4 rows=3D9656 width=3D214) (actual time=3D417.603..8644.294 rows=3D399879 l= oops=3D1) -> Nested Loop (cost=3D21825.09..2= 1837.50 rows=3D373 width=3D195) (actual time=3D417.444..672.741 rows=3D1415= 8 loops=3D1) -> Seq Scan on sces_gtr sces_= gtr_art (cost=3D0.00..1.44 rows=3D1 width=3D40) (actual time=3D0.026..0.08= 5 rows=3D1 loops=3D1) Filter: (gtr_cod =3D 2::= numeric) -> Merge Join (cost=3D21825.= 09..21832.34 rows=3D373 width=3D165) (actual time=3D417.400..568.247 rows= =3D14158 loops=3D1) Merge Cond: ("outer".fsf= _codfam =3D "inner".fsf_codfam) -> Sort (cost=3D255.24= ..255.30 rows=3D24 width=3D74) (actual time=3D16.597..16.692 rows=3D106 loo= ps=3D1) Sort Key: sces_fam= ille.fsf_codfam -> Seq Scan on sc= es_fsf sces_famille (cost=3D0.00..254.69 rows=3D24 width=3D74) (actual tim= e=3D0.029..15.971 rows=3D155 loops=3D1) Filter: ((fs= f_codseg =3D 0::numeric) AND (fsf_codsfm =3D 0::numeric)) -> Sort (cost=3D21569.= 85..21571.64 rows=3D715 width=3D91) (actual time=3D400.631..416.871 rows=3D= 14162 loops=3D1) Sort Key: sces_art= .fsf_codfam -> Nested Loop (= cost=3D0.00..21535.95 rows=3D715 width=3D91) (actual time=3D1.320..230.975 = rows=3D14162 loops=3D1) -> Seq Scan= on sces_frc sces_frc_art (cost=3D0.00..182.75 rows=3D728 width=3D51) (act= ual time=3D1.195..14.316 rows=3D761 loops=3D1) Filter= : (2::numeric =3D gtr_cod) -> Index Sc= an using ind_art_02 on sces_art (cost=3D0.00..29.24 rows=3D7 width=3D61) (= actual time=3D0.040..0.160 rows=3D19 loops=3D761) Index = Cond: ((2::numeric =3D sces_art.gtr_cod) AND ("outer".fou_cod =3D sces_art.= fou_cod)) -> Index Scan using idx_vte_02 on s= ces_vte (cost=3D0.00..6.01 rows=3D1 width=3D62) (actual time=3D0.037..0.25= 9 rows=3D28 loops=3D14158) Index Cond: ((sces_vte.art_cod= =3D "outer".art_cod) AND (sces_vte.dos_cod =3D "outer".dos_cod)) Total runtime: 545435.989 ms