Date: Mon, 1 Apr 2019 02:45:14 -0700 (MST)
From: "tank.zhang" <6220104@qq.com>
To: pgsql-performance@postgresql.org
Message-ID: <1554111914781-0.post@n3.nabble.com>
Subject: Postgresql Sort cost Poor performance?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

1=E3=80=81postgresql version

qis3_dp2=3D> select * from version();
                                                 version                   =
                             =20
---------------------------------------------------------------------------=
------------------------------
 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=3D>=20

2=E3=80=81postgresql work_mem=20


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

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

qis3_dp2=3D>=20

3=E3=80=81Table count

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

qis3_dp2=3D>=20

4=E3=80=81table desc

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

qis3_dp2=3D>=20

5=E3=80=81Execute SQL:
qis3_dp2=3D> EXPLAIN (analyze true,buffers true)   SELECT COUNT(DISTINCT SV=
IN)
AS CHECKCARNUM ,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=3D1 AND A.SSTATIO=
NCD
=3D 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >=3D
TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <=3D
TO_DATE('2019-03-11','YYYY-MM-DD') group by  SMTOC
;
                                                                           =
                                             =20
QUERY PLAN                                                                 =
            =20
                                            =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------
---------------------------------------------
 GroupAggregate  (cost=3D697738.61..714224.02 rows=3D372 width=3D30) (actua=
l
time=3D5908.786..32420.412 rows=3D410 loops=3D1)
   Group Key: smtoc
   Buffers: shared hit=3D401 read=3D184983
   I/O Timings: read=3D1377.762
   ->  Sort  (cost=3D697738.61..703232.51 rows=3D2197559 width=3D40) (actua=
l
time=3D5907.791..6139.351 rows=3D2142215 loops=3D1)
         Sort Key: smtoc
         Sort Method: quicksort  Memory: 265665kB
         Buffers: shared hit=3D401 read=3D184983
         I/O Timings: read=3D1377.762
         ->  Gather  (cost=3D1000.00..466253.56 rows=3D2197559 width=3D40) =
(actual
time=3D0.641..1934.614 rows=3D2142215 loops=3D1)
               Workers Planned: 5
               Workers Launched: 5
               Buffers: shared hit=3D401 read=3D184983
               I/O Timings: read=3D1377.762
               ->  Parallel Seq Scan on qis_carpassedstation a=20
(cost=3D0.00..245497.66 rows=3D439512 width=3D40) (actual time=3D0.245..194=
0.527
rows=3D357036 loops=3D6)
                     Filter: (((sstationcd)::text =3D 'VQ3_LYG'::text) AND
((slineno)::text =3D ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >=3D
to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <=3D to_da
te('2019-03-11'::text, 'YYYY-MM-DD'::text)))
                     Rows Removed by Filter: 1551811
                     Buffers: shared hit=3D401 read=3D184983
                     I/O Timings: read=3D1377.762
 Planning Time: 0.393 ms
 Execution Time: 32439.704 ms
(21 rows)

qis3_dp2=3D>=20


6=E3=80=81Why does sort take a long time to execute and how can you optimiz=
e it?
Thanks!!!


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f205008=
1.html