pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: tank.zhang <6220104@qq.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgresql Sort cost Poor performance?
Date: Tue, 2 Apr 2019 01:23:50 -0700 (MST)
Message-ID: <1554193430839-0.post@n3.nabble.com> (raw)
In-Reply-To: <CAKJS1f8aMw0Fbd7K1D4n08fRMUS_19D9pTNXd6u9Qi+eMj6wOQ@mail.gmail.com>
References: <1554111914781-0.post@n3.nabble.com>
	<CADGuD5DDHfwsU+6MjwfmvUSYHc3K7bDe9Tf7jS41ybTOpQgxag@mail.gmail.com>
	<1554188409091-0.post@n3.nabble.com>
	<CAKJS1f8aMw0Fbd7K1D4n08fRMUS_19D9pTNXd6u9Qi+eMj6wOQ@mail.gmail.com>

Thank you  replay!

I tried to use the TMP table is very fast .    thank you


qis3_dp2=# explain analyze SELECT COUNT(*),SMTOC FROM ( SELECT
DISTINCT(SVIN) AS CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=1
AND A.SSTATIONCD = 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND
A.DWORKDATE >= TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=
TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP  group by SMTOC;
                                                                                                                         
QUERY PLAN                                                                              
                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------
 HashAggregate  (cost=691386.41..691388.41 rows=200 width=30) (actual
time=4090.951..4091.027 rows=410 loops=1)
   Group Key: a.smtoc
   ->  HashAggregate  (cost=666561.44..676491.43 rows=992999 width=40)
(actual time=3481.712..3794.213 rows=1071367 loops=1)
         Group Key: a.svin, a.smtoc
         ->  Gather  (cost=1000.00..656098.93 rows=2092501 width=40) (actual
time=0.657..1722.814 rows=2142215 loops=1)
               Workers Planned: 4
               Workers Launched: 4
               ->  Parallel Seq Scan on qis_carpassedstation a 
(cost=0.00..445848.83 rows=523125 width=40) (actual time=65.187..2287.739
rows=428443 loops=5)
                     Filter: (((sstationcd)::text = 'VQ3_LYG'::text) AND
((slineno)::text = ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >=
to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <= 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=# 


qis3_dp2=# SELECT COUNT(*),SMTOC FROM ( SELECT DISTINCT(SVIN) AS
CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=1 AND A.SSTATIONCD =
'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=
TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=
TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP  group by SMTOC;

**Time: 3223.935 ms (00:03.224)**


2、 Before 

qis3_dp2=# explain analyze SELECT COUNT(DISTINCT SVIN) AS CHECKCARNUM ,SMTOC
FROM QIS_CARPASSEDSTATION A WHERE 1=1 AND A.SSTATIONCD = 'VQ3_LYG' AND
A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=
TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=
TO_DATE('2019-03-11','YYYY-MM-DD') group by  SMTOC;
                                                                                                                         
QUERY PLAN                                                                              
                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------
 GroupAggregate  (cost=875778.02..891475.55 rows=377 width=30) (actual
time=6400.991..33314.132 rows=410 loops=1)
   Group Key: smtoc
   ->  Sort  (cost=875778.02..881009.28 rows=2092501 width=40) (actual
time=6399.993..6626.151 rows=2142215 loops=1)
         Sort Key: smtoc
         Sort Method: quicksort  Memory: 265665kB
         ->  Gather  (cost=1000.00..656098.93 rows=2092501 width=40) (actual
time=0.557..2467.778 rows=2142215 loops=1)
               Workers Planned: 4
               Workers Launched: 4
               ->  Parallel Seq Scan on qis_carpassedstation a 
(cost=0.00..445848.83 rows=523125 width=40) (actual time=66.908..2428.397
rows=428443 loops=5)
                     Filter: (((sstationcd)::text = 'VQ3_LYG'::text) AND
((slineno)::text = ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >=
to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <= 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=# 











--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html





view thread (9+ messages)

Message-ID: <1554193430839-0.post@n3.nabble.com>
Permalink:  ../1554193430839-0.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1554193430839-0.post@n3.nabble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: 6220104@qq.com
  Subject: Re: Postgresql Sort cost Poor performance?
  In-Reply-To: <1554193430839-0.post@n3.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox