Re: Postgresql Sort cost Poor performance?

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