From: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
To: liam saffioti <liam.saffiotti@gmail.com>
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
To: Julien Rouhaud <rjuju123@gmail.com>
Subject: AW: postgresql long running query
Date: Fri, 3 Dec 2021 11:39:38 +0000
Message-ID: <8718fe01e83140b18f7bc8777469c44d@MITMB5.helios.med.uni-muenchen.de> (raw)
In-Reply-To: <CAGHjuaZkGZVRK6OC4ifBJVQ+=1Y=0CXdDnZ7sjHkdfY2wkACVg@mail.gmail.com>
References: <CAGHjuaZkGZVRK6OC4ifBJVQ+=1Y=0CXdDnZ7sjHkdfY2wkACVg@mail.gmail.com>
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@gmail.com>
Gesendet: Freitag, 3. Dezember 2021 12:24
An: pgsql-admin <pgsql-admin@lists.postgresql.org>; Julien Rouhaud <rjuju123@gmail.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Anton.Dischner@med.uni-muenchen.de, liam.saffiotti@gmail.com, pgsql-admin@lists.postgresql.org, rjuju123@gmail.com
Subject: Re: AW: postgresql long running query
In-Reply-To: <8718fe01e83140b18f7bc8777469c44d@MITMB5.helios.med.uni-muenchen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox