Re: Query optimization

On Thu, Mar 13, 2025 at 11:49 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > To return one row takes 43ms is not optimal > It's actually much faster than that, but even 43ms is overall good. The query is already pretty optimal, as it uses a single index only scan. There are a few tric

Re: Query optimization

On Fri, 14 Mar, 2025, 09:11 Ron Johnson, wrote: > On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson >> wrote: >> >>> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < >>> maheshpostgr...@gmail.com> wrote:

Re: Query optimization

> > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) >InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 > rows=1 width=0) (actual time=0.029..0.029 rows=1 lo

Re: Query optimization

> On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne > wrote: > > > > On Fri, 14 Mar, 2025, 08:04 Rob Sargent, > wrote: > > Hi > > Gave you plan with out limit . Stats up to date insert .on conflict do > nothing runs some times > > Regards > Durga Mahesh

Re: Query optimization

On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > On Fri, 14 Mar, 2025, 09:11 Ron Johnson, wrote: > >> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> >>> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson >>> wrote: >>

Re: Query optimization

On Thursday, March 13, 2025, Durgamahesh Manne wrote: > > > > > >

Re: Query optimization

On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson > wrote: > >> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> [snip] >> >>> Hi Adrian Klaver >>> >>> 1) Postgres versio

Re: Query optimization

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson wrote: > On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > [snip] > >> Hi Adrian Klaver >> >> 1) Postgres version. >> select version(); >> version >> >> --

Re: Query optimization

On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: [snip] > Hi Adrian Klaver > > 1) Postgres version. > select version(); > version > > -

Re: Query optimization

On Fri, 14 Mar, 2025, 08:04 Rob Sargent, wrote: > > > > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) >InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys > (cost=0.55..2.80 row

Re: Query optimization

On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver wrote: > On 3/13/25 12:12, Durgamahesh Manne wrote: > > Hi Team > > > > This query takes more time than usual for execution > > Define usual. > > > > How to optimize it in best possible way > > Can't be answered without, to start: > > 1) Postgres ver

Re: Query optimization

On Fri, 2025-03-14 at 00:42 +0530, Durgamahesh Manne wrote: > This query takes more time than usual for execution  > > How to optimize it in best possible way > > Columns used in this query >> composite index eventhough not running > optimally  > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE Ca

Re: Query optimization

On 3/13/25 12:12, Durgamahesh Manne wrote: Hi Team This query takes more time than usual for execution Define usual. How to optimize it in best possible way Can't be answered without, to start: 1) Postgres version. 2) Complete(including indexes) table schema. 3) Output of EXPLAIN ANALY

Query optimization

Hi Team This query takes more time than usual for execution How to optimize it in best possible way Columns used in this query >> composite index eventhough not running optimally SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND

Re: Configuration knobs & dials to speed up query optimization

Am 22.11.23 um 17:13 schrieb Ron Johnson: Pg 9.6.24, which will change by April, but not now. out of support since many years! Here are the only non-default config values which I can think of that are relevant to the question at hand: shared_buffers = 16GB work_mem = 300MB maintenanc

Re: Configuration knobs & dials to speed up query optimization

On Thu, Nov 23, 2023 at 3:48 AM Laurenz Albe wrote: > On Wed, 2023-11-22 at 11:13 -0500, Ron Johnson wrote: > > Pg 9.6.24, which will change by April, but not now. > > > > We've got some huge (2200 line long) queries that are many UNIONs > of complicated > > queries hitting inheritance-partitione

Re: Configuration knobs & dials to speed up query optimization

On Wed, 2023-11-22 at 11:13 -0500, Ron Johnson wrote: > Pg 9.6.24, which will change by April, but not now. > > We've got some huge (2200 line long) queries that are many UNIONs of  > complicated > queries hitting inheritance-partitioned tables.  They can't be refactored > immediately, > and mayb

Configuration knobs & dials to speed up query optimization

Pg 9.6.24, which will change by April, but not now. We've got some huge (2200 line long) queries that are many UNIONs of complicated queries hitting inheritance-partitioned tables. They can't be refactored immediately, and maybe not at all (complicated applications hitting normalized databases ma

Re: Need help on query optimization

> > > Here B is a ltree column, E is a jsonb column. > It may also help to mention the indexes and their types. eg. Does column B have a GiST index? > > EXPLAIN ANALYZE SELECT * FROM A > > where ( B <@ 'INDIA' ) AND C = 'D' > > AND CAST ( E->'F'->'G'->>'H' AS DATE ) >= '2021-02-01' > > AND CA

Need help on query optimization

Hi Team, I am trying to execute the below query and getting below explain plan in postgres . Please provide any inputs on the same , on how to optimize the same. Here B is a ltree column, E is a jsonb column. EXPLAIN ANALYZE SELECT * FROM A where ( B <@ 'INDIA' ) AND C = 'D' AND CAST ( E->'F'

Re: Apparent missed query optimization with self-join and inner grouping

Zack Weinberg writes: > For each (experiment_id, url_id) pair for some small subset of the > experiment_ids, I need to query the full_url_id corresponding to the > *largest* value of redirect_num. The query planner does something > reasonable with this SELECT: > => explain (analyze, verbose) >

Re: Apparent missed query optimization with self-join and inner grouping

Subqueries are evaluated separately when they include an aggregate or window function as I understand it. I agree that it would be helpful in your case if that outside qual was pushed inside. I assume this query is generated by an ORM and you don't just have the option to simply change it?

Apparent missed query optimization with self-join and inner grouping

I have a table recording the results of a web crawl. (Table definition at the end of this message.) The relevant part of the data stored in it looks like this: id | url_id | full_url_id | experiment_id | redirect_num --++-+---+-- 2617 | 1312

Re: Query optimization with repeated calculations

Robert McGehee writes: > I frequently want to make views that repeat a calculation over and over > again in lots of columns. In the example below, let’s say it’s (a+b), but > we can imagine the calculation being much more complicated. > For example: > CREATE VIEW AS > SELECT (a+b)*c as c1, (a+b)*

Query optimization with repeated calculations

Hello, I have a general query optimization question involving repeated calculations. I frequently want to make views that repeat a calculation over and over again in lots of columns. In the example below, let’s say it’s (a+b), but we can imagine the calculation being much more complicated. For