Re: Postgresql Sort cost Poor performance?

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(at)qq(dot)com> 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
>
>
>