Re: postgresql long running query

Hi Anton and Samed,
Thank you for your reply.
I use PostgreSQL 12.9 on RHEL 8.4.
There are no DDL queries on the database in a whole day, so there is no
lock state.
The pgbadger slow queries report shows this query ran for 43min 11sec.
And table and indexes definition is:

\d+ "T_CMN_SLAHISTORY"
Table
"T_CMN_SLAHISTORY"
Column | Type | Collation | Nullable |
Default | Storage | Stats target | Description
------------------+-----------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
HistoryId | integer | | not null |
generated by default as identity | plain | |
DefinitionId | integer | | not null |
| plain | |
RuleName | character varying(200) | | not null |
| extended | |
CreateUserId | integer | | not null |
| plain | |
CreateUserTime | timestamp without time zone | | not null |
| plain | |
LastRunningTime | timestamp without time zone | | |
| plain | |
Cost | integer | | |
| plain | |
UpdateUserId | integer | | |
| plain | |
UpdateUserTime | timestamp without time zone | | |
| plain | |
RecId | character varying(36) | | |
| extended | |
MyTimeStamp | bytea | | |
| extended | |
IsOldRecordBatch | boolean | | |
| plain | |
InstanceId | bigint | | |
| plain | |
Indexes:
"T_CMN_SLAHISTORY_HistoryId_idx" UNIQUE, btree ("HistoryId")
"Index-20180207-152712" btree ("RuleName", "DefinitionId")
"Index-20180712-192739" btree ("DefinitionId", "IsOldRecordBatch")
"Index-20180712-192836" btree ("InstanceId")
Access method: heap

Schema | Name | Type | Owner | Size | Description
---------+------------------+-------+----------+--------+-------------
public | T_CMN_SLAHISTORY | table | postgres | 244 MB |

Dischner, Anton <Anton(dot)Dischner(at)med(dot)uni-muenchen(dot)de>, 3 Ara 2021 Cum, 14:39
tarihinde şunu yazdı:

> Hi Liam,
>
>
>
> top tuning tip for every relational database is: indexes!
>
>
>
> I have adminstrated databases where data was 300 GB and created indexes
> 600 GB
>
> You may think thats useless redundant but ist definitively not.
>
>
>
> Rules for indexing:
>
>
>
> Does it make sense to create an index for a table with one or several
> douzends of entries? -> YES!
>
> Does it make sense to create an index that is optimized for certain
> queries? -> YES!
>
> Does it make sense to create an index, make the query and drop the index?
> -> YES
>
>
>
> Even with tables with billions of entries you should get answer times of
> less than a second!
>
> … if you have a good index: date for example where the database engine
> plays ist full potental.
>
>
>
> For example see:
> https://www.enterprisedb.com/postgres-tutorials/overview-postgresql-indexes
>
>
>
> Which indexes do you have?
>
>
>
> best,
>
>
>
> A
>
>
>
> *Von:* liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
> *Gesendet:* Freitag, 3. Dezember 2021 12:24
> *An:* pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>; Julien Rouhaud <
> rjuju123(at)gmail(dot)com>
> *Betreff:* postgresql long running query
>
>
>
> Hello Team,
>
>
>
> I have a problem with a query that consumes a long time.
>
> The query' execution plan is :
>
>
>
>
>
> EXPLAIN ANALYZE SELECT x."HistoryId", x."SlaDefinition" FROM
> "T_CMN_SLAHISTORY" AS x WHERE x."DefinitionId" = '302';
>
> QUERY PLAN
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on "T_CMN_SLAHISTORY" x (cost=2144.77..35086.42
> rows=139012 width=8) (actual time=58.806..116.874 rows=135498 loops=1)
> Recheck Cond: ("DefinitionId" = 302)
> Heap Blocks: exact=2175
> -> Bitmap Index Scan on "Index-20180712-192739" (cost=0.00..2110.02
> rows=139012 width=0) (actual time=57.043..57.045 rows=135498 loops=1)
> Index Cond: ("DefinitionId" = 302)
> Planning Time: 11.132 ms
> Execution Time: 120.320 ms
>
>
>
>
>
>
>
> But, the query execution time was 43min 11seconds in the morning in
> pgbadger report. I don't understand why the query is taking so long. Can
> you guide me?
>
>
>
> Thank you so much.
>
>
>