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
