Re: Postgresql Sort cost Poor performance?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux