Hi,
If your problem is the sort, try creating an index on the Field that you consider thst could be needed (you can star with smtoc that is the one you are grouping and sorting)
Another thing that i noticed is your work_mem, I thing is too high for a global config (if you think 2gb can hel for this operation you can set it before execute the query but only for that session), but generally this value most be smaller depending on the commons query every sub query uses that amount of mem (i.e if you have a query that have 3 subqueries and each one with a sort operation and a grouping operation, you can be using 12 gb of mem in that only big query, and it doesn't mean it will be faster).. try to monitor the uses of ram by pgsql maybe you can be suffering paging problems because os the size of you work_mem and that make the dbms slow too
On Mon, Apr 1, 2019, 6:45 AM tank.zhang <6220104@xxxxxx> wrote:
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
