Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hBEi8-0002Uf-3S for pgsql-performance@arkaria.postgresql.org; Tue, 02 Apr 2019 08:24:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hBEi6-0000hG-5A for pgsql-performance@arkaria.postgresql.org; Tue, 02 Apr 2019 08:23:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from <6220104@qq.com>) id 1hBEi5-0000h9-S9 for pgsql-performance@lists.postgresql.org; Tue, 02 Apr 2019 08:23:57 +0000 Received: from n3.nabble.com ([162.255.23.22]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from <6220104@qq.com>) id 1hBEi0-0007kB-I7 for pgsql-performance@postgresql.org; Tue, 02 Apr 2019 08:23:56 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id CD6B21300F3A5 for ; Tue, 2 Apr 2019 01:23:50 -0700 (MST) Date: Tue, 2 Apr 2019 01:23:50 -0700 (MST) From: "tank.zhang" <6220104@qq.com> To: pgsql-performance@postgresql.org Message-ID: <1554193430839-0.post@n3.nabble.com> In-Reply-To: References: <1554111914781-0.post@n3.nabble.com> <1554188409091-0.post@n3.nabble.com> Subject: Re: Postgresql Sort cost Poor performance? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Thank you replay! I tried to use the TMP table is very fast . thank you qis3_dp2=3D# explain analyze SELECT COUNT(*),SMTOC FROM ( SELECT DISTINCT(SVIN) AS CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=3D1 AND A.SSTATIONCD =3D 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=3D TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=3D TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP group by SMTOC; = =20 QUERY PLAN = =20 =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------ --------------------------------------------- HashAggregate (cost=3D691386.41..691388.41 rows=3D200 width=3D30) (actual time=3D4090.951..4091.027 rows=3D410 loops=3D1) Group Key: a.smtoc -> HashAggregate (cost=3D666561.44..676491.43 rows=3D992999 width=3D40= ) (actual time=3D3481.712..3794.213 rows=3D1071367 loops=3D1) Group Key: a.svin, a.smtoc -> Gather (cost=3D1000.00..656098.93 rows=3D2092501 width=3D40) = (actual time=3D0.657..1722.814 rows=3D2142215 loops=3D1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on qis_carpassedstation a=20 (cost=3D0.00..445848.83 rows=3D523125 width=3D40) (actual time=3D65.187..22= 87.739 rows=3D428443 loops=3D5) Filter: (((sstationcd)::text =3D 'VQ3_LYG'::text) AND ((slineno)::text =3D ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >=3D to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <=3D to_da te('2019-03-11'::text, 'YYYY-MM-DD'::text))) Rows Removed by Filter: 1862173 Planning Time: 0.513 ms Execution Time: 4147.542 ms (12 rows) Time: 4148.852 ms (00:04.149) qis3_dp2=3D#=20 qis3_dp2=3D# SELECT COUNT(*),SMTOC FROM ( SELECT DISTINCT(SVIN) AS CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=3D1 AND A.SSTATIONCD = =3D 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=3D TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=3D TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP group by SMTOC; **Time: 3223.935 ms (00:03.224)** 2=E3=80=81 Before=20 qis3_dp2=3D# explain analyze SELECT COUNT(DISTINCT SVIN) AS CHECKCARNUM ,SM= TOC FROM QIS_CARPASSEDSTATION A WHERE 1=3D1 AND A.SSTATIONCD =3D 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=3D TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=3D TO_DATE('2019-03-11','YYYY-MM-DD') group by SMTOC; = =20 QUERY PLAN = =20 =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------ --------------------------------------------- GroupAggregate (cost=3D875778.02..891475.55 rows=3D377 width=3D30) (actua= l time=3D6400.991..33314.132 rows=3D410 loops=3D1) Group Key: smtoc -> Sort (cost=3D875778.02..881009.28 rows=3D2092501 width=3D40) (actua= l time=3D6399.993..6626.151 rows=3D2142215 loops=3D1) Sort Key: smtoc Sort Method: quicksort Memory: 265665kB -> Gather (cost=3D1000.00..656098.93 rows=3D2092501 width=3D40) = (actual time=3D0.557..2467.778 rows=3D2142215 loops=3D1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on qis_carpassedstation a=20 (cost=3D0.00..445848.83 rows=3D523125 width=3D40) (actual time=3D66.908..24= 28.397 rows=3D428443 loops=3D5) Filter: (((sstationcd)::text =3D 'VQ3_LYG'::text) AND ((slineno)::text =3D ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >=3D to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <=3D to_da te('2019-03-11'::text, 'YYYY-MM-DD'::text))) Rows Removed by Filter: 1862173 Planning Time: 0.457 ms Execution Time: 33335.429 ms (13 rows) * Time: 33336.720 ms (00:33.337)* qis3_dp2=3D#=20 -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f205008= 1.html