AW: postgresql long running query

["Dischner, Anton" <Anton.Dischner@xxxxxxxxxxxxxxxxxxx>]

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.saffiotti@xxxxxxxxx>
Gesendet: Freitag, 3. Dezember 2021 12:24
An: pgsql-admin <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>; Julien Rouhaud <rjuju123@xxxxxxxxx>
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.