Postgresql Sort cost Poor performance? - Mailing list pgsql-performance

1、postgresql version

qis3_dp2=> select * from version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

qis3_dp2=>

2、postgresql work_mem


qis3_dp2=> SHOW work_mem;
 work_mem
----------
 2GB
(1 row)

qis3_dp2=> SHOW  shared_buffers;
 shared_buffers
----------------
 4028MB
(1 row)

qis3_dp2=>

3、Table count

qis3_dp2=> select count(*) from  QIS_CARPASSEDSTATION;
  count
----------
 11453079
(1 row)

qis3_dp2=>

4、table desc

qis3_dp2=> \dS QIS_CARPASSEDSTATION;
                   Table "qis_schema.qis_carpassedstation"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 iid          | integer                     |           | not null |
 scartypecd   | character varying(50)       |           |          |
 svin         | character varying(20)       |           |          |
 sstationcd   | character varying(50)       |           |          |
 dpassedtime  | timestamp(6) with time zone |           |          |
 dworkdate    | date                        |           |          |
 iworkyear    | integer                     |           |          |
 iworkmonth   | integer                     |           |          |
 iweek        | integer                     |           |          |
 sinputteamcd | character varying(20)       |           |          |
 sinputdutycd | character varying(20)       |           |          |
 smtoc        | character varying(50)       |           |          |
 slineno      | character varying(18)       |           |          |
Indexes:
    "qis_carpassedstation_pkey" PRIMARY KEY, btree (iid)
    "q_carp_dworkdate" btree (dworkdate)
    "q_carp_smtoc" btree (smtoc)

qis3_dp2=>

5、Execute SQL:
qis3_dp2=> EXPLAIN (analyze true,buffers true)   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=697738.61..714224.02 rows=372 width=30) (actual
time=5908.786..32420.412 rows=410 loops=1)
   Group Key: smtoc
   Buffers: shared hit=401 read=184983
   I/O Timings: read=1377.762
   ->  Sort  (cost=697738.61..703232.51 rows=2197559 width=40) (actual
time=5907.791..6139.351 rows=2142215 loops=1)
         Sort Key: smtoc
         Sort Method: quicksort  Memory: 265665kB
         Buffers: shared hit=401 read=184983
         I/O Timings: read=1377.762
         ->  Gather  (cost=1000.00..466253.56 rows=2197559 width=40) (actual
time=0.641..1934.614 rows=2142215 loops=1)
               Workers Planned: 5
               Workers Launched: 5
               Buffers: shared hit=401 read=184983
               I/O Timings: read=1377.762
               ->  Parallel Seq Scan on qis_carpassedstation a
(cost=0.00..245497.66 rows=439512 width=40) (actual time=0.245..1940.527
rows=357036 loops=6)
                     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: 1551811
                     Buffers: shared hit=401 read=184983
                     I/O Timings: read=1377.762
 Planning Time: 0.393 ms
 Execution Time: 32439.704 ms
(21 rows)

qis3_dp2=>


6、Why does sort take a long time to execute and how can you optimize it?
Thanks!!!






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