Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsvuo-006uGP-5B for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:40:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tsvum-00Dhmf-Ig for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:40:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsvul-00Dhim-V3 for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:40:52 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsvuj-002j50-2L for pgsql-general@postgresql.org; Fri, 14 Mar 2025 03:40:51 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-601e049d794so259241eaf.3 for ; Thu, 13 Mar 2025 20:40:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741923649; x=1742528449; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Zhx9OvbUzQ3yu/+LlLz1qOWbjhEIp6ql5c2KJVbzAXA=; b=PncZ86AZIVwSDwnzkTtLH1OemS9N403EsKcLjvwcVTZ8reHi86pfNrOXKkIVNsCzjL BBaT+uHHz9YzPssUFTpD5n/x8KWmoQYLq63xnTc2qe9uyDtsn/PScwjZ92sMZDbKi6gc 8f25ybscFf/l3JuKrJC/wkrq1M17rvxWWnrHfKZ4QRLR7T35OxGtTtYJl8Q4rpVfkxra 6nSgq8MJQLpN/qkDxIjbK5rVOcliBwZybUv8sBLcxJmDmGSuCpTq/Yukaa6rxY5pip9L pXbrTFGq7IWAWAB4gBEokZSuAehOQAJDss4gyl6YDNwmqceGR6sMRmXo06uMO2gwOSPY eh3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741923649; x=1742528449; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Zhx9OvbUzQ3yu/+LlLz1qOWbjhEIp6ql5c2KJVbzAXA=; b=omI9b+OG/io0lcVA9IZri7OVB6Cw1yP1QjZbSftp+whTs2ufkxiIyIlOt3wlQZOCwz FrssU9/AFV96jmhC2yPpnGR8IuMhwGrreGYtG5ugfXl2KDnDw3NvaDEc8l1VicyI6fQP GslwDjb13epa3xIGovqOaDAMRF91Y+9C4aOgMt0IT4akKoeugc8+E/23tBRCX91n4elI JSprT3WY8sonLY1FBjgj+oYZsZAYo+Ne6ghixutrn70vfjRGarO6Bt3zgltwlcfRIIR3 9+nij5huLNlez9+wlIId23OyCuYrJm5l0/BYnW/YBt6QVxiu6Z8vbxU3CREz5uziMDhT /9fQ== X-Gm-Message-State: AOJu0Yzz8O+ve6bn8+/g+bju4+f2if2l+xxiI9EAWD9G4F4AwCJiXDWi ZerF2/ZnfHA7hRikDDccHPPdcmNvBA2Wzct6+m8nZk81NRNFcD14M8POb7Gul04/NFR+IyAW0oR 1OGqEPpMLtbcpc4PJtV/uk+xolwLtl5Ym X-Gm-Gg: ASbGncus2/gt/8vvgE8AFFb+y9GuseRyrGusZLrkQaqiwrragmzBKMoft3kZR0xSuZE pY8QOGVXo91HvwSOCnEeTVyGrE2Wjpua1VrmF9EkkYR9ad0np2u+ukp7Gi/VqaRR6gps8pV0pnp 9QDD002zfS9m185YMn2L1Xl5uw7Cn2X2jJ5FfadREO6jjKZMAvSIskjke+cMZ6 X-Google-Smtp-Source: AGHT+IGh0c4IjTjh/1i54tANB4TJKJrovLp9FfSBf2zVAt/Ob0ZfeiJ+L32Kl74WXRYlmXaHA+D0pohC0IQwWesNP9Q= X-Received: by 2002:a4a:ee86:0:b0:601:bf4d:86ef with SMTP id 006d021491bc7-601e45478f3mr567827eaf.2.1741923648803; Thu, 13 Mar 2025 20:40:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 13 Mar 2025 23:40:37 -0400 X-Gm-Features: AQ5f1Jp-wTexUbieIyoisgKPeZDT1kcDH_DQ3q7NJC_RqvhoMeXiK7eKiClX6J4 Message-ID: Subject: Re: Query optimization To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000005020ba0630453483" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005020ba0630453483 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 13, 2025 at 11:25=E2=80=AFPM Durgamahesh Manne < maheshpostgres9@gmail.com> wrote: > On Fri, Mar 14, 2025 at 8:19=E2=80=AFAM Ron Johnson > wrote: > >> On Thu, Mar 13, 2025 at 10:16=E2=80=AFPM Durgamahesh Manne < >> maheshpostgres9@gmail.com> wrote: >> [snip] >> >>> Hi Adrian Klaver >>> >>> 1) Postgres version. >>> select version(); >>> version >>> >>> -----------------------------------------------------------------------= ---------------------------------------- >>> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) >>> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit >>> >>> 2) Complete(including indexes) table schema. >>> >>> Table >>> "liveaggregations.cachekeys" >>> Column | Type | Collation | Nullable | Defaul= t >>> | Storage | Compression >>> >>> ---------------+------------------------+-----------+----------+-------= --+----------+------------ >>> cachetype | character varying(255) | | | >>> | extended | >>> trsid | character varying(255) | | | >>> | extended | >>> brandid | character varying(255) | | | >>> | extended | >>> sportid | character varying(255) | | | >>> | extended | >>> competitionid | character varying(255) | | | >>> | extended | >>> eventid | character varying(255) | | | >>> | extended | >>> marketid | character varying(255) | | | >>> | extended | >>> selectionid | character varying(255) | | | >>> | extended | >>> keytype | character varying(255) | | | >>> | extended | >>> key | character varying(255) | | not null | >>> | extended | >>> Indexes: >>> "cachekeys_key_pk" PRIMARY KEY, btree (key) >>> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid, >>> competitionid, eventid, marketid) >>> "idx_marketid" btree (marketid) >>> >>> 3) Output of EXPLAIN ANALYZE of query. >>> >>> Result (cost=3D2.80..2.83 rows=3D1 width=3D1) (actual time=3D0.030..0= .030 >>> rows=3D1 loops=3D1) >>> InitPlan 1 (returns $0) >>> -> Index Only Scan using idx_cachekeys on cachekeys >>> (cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time=3D0.029..0.029 row= s=3D1 loops=3D1) >>> Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D >>> 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (sportid =3D >>> 'BOX'::text) AND (competitionid =3D 'U-1998'::text) AND (eventid =3D >>> 'U-523596'::text)) >>> Heap Fetches: 0 >>> Planning Time: 0.221 ms >>> Execution Time: 0.046 ms >>> >> >> That looks pretty reasonable. >> >> 1. Now show what happens with the LIMIT clause. >> 2. How many rows does it return? >> 3. Do you keep the table regularly vacuumed and analyzed? >> >> Hey Ron > > 1. Now show what happens with the LIMIT clause. > and result set of query and *Size of the table 287MB* > exists > -------- > t > (1 row) > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------- > Result (cost=3D2.80..2.83 rows=3D1 width=3D1) (actual time=3D0.029..0.0= 30 rows=3D1 > loops=3D1) > InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys > (cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time=3D0.028..0.028 rows= =3D1 loops=3D1) > Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D > 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (sportid =3D > 'BOX'::text) AND (competitionid =3D 'U-1998'::text) AND (eventid =3D > 'U-523596'::text)) > Heap Fetches: 1 > Planning Time: 0.084 ms > Execution Time: 0.043 ms > This might be due to caching. Run the query with LIMIT three times, and then remove the LIMIT and run three times. Honestly, though, the execution timings seem pretty good. What exactly is the problem? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000005020ba0630453483 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Mar 13, 2025 at 11:25=E2=80=AFPM = Durgamahesh Manne <maheshpo= stgres9@gmail.com> wrote:
On Fri, Mar 14, 2025 at 8:19=E2=80=AFAM Ron Johnson &l= t;ronljohnsonj= r@gmail.com> wrote:
On Thu,= Mar 13, 2025 at 10:16=E2=80=AFPM Durgamahesh Manne <maheshpostgres9@gmail.com&g= t; wrote:
[snip]=C2=A0
Hi Adrian Klaver

1) Postgres v= ersion.
=C2=A0select version();
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 version
------------------------------------= ---------------------------------------------------------------------------=
=C2=A0PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (G= CC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

2) Complete(including i= ndexes) table schema.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "liveaggregations.cach= ekeys"
=C2=A0 =C2=A0 Column =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Collation | Nullable = | Default | Storage =C2=A0| Compression
---------------+----------------= --------+-----------+----------+---------+----------+------------
=C2=A0= cachetype =C2=A0 =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0trsid = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0bran= did =C2=A0 =C2=A0 =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0spor= tid =C2=A0 =C2=A0 =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0comp= etitionid | character varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended = | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0eventid =C2=A0 =C2=A0 = =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0marketid =C2=A0 =C2=A0 = =C2=A0| character varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0selectionid =C2=A0 | cha= racter varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0keytype =C2=A0 =C2=A0 =C2=A0 | cha= racter varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0key =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | character varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not = null | =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0
Indexes:
=C2=A0 =C2=A0 "cachekeys_key_pk" PR= IMARY KEY, btree (key)
=C2=A0 =C2=A0 "idx_cachekeys" btree (ca= chetype, trsid, brandid, sportid, competitionid, eventid, marketid)
=C2= =A0 =C2=A0 "idx_marketid" btree (marketid)

3) Output of EXPLAIN ANALYZE of query.

=C2=A0Result =C2=A0(cost=3D2= .80..2.83 rows=3D1 width=3D1) (actual time=3D0.030..0.030 rows=3D1 loops=3D= 1)
=C2=A0 =C2=A0InitPlan 1 (returns $0)
=C2=A0 =C2=A0 =C2=A0-> =C2= =A0Index Only Scan using idx_cachekeys on cachekeys =C2=A0(cost=3D0.55..2.8= 0 rows=3D1 width=3D0) (actual time=3D0.029..0.029 rows=3D1 loops=3D1)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((cachetype =3D 'B= oMatrix'::text) AND (trsid =3D 'daznbetuk'::text) AND (brandid = =3D 'daznbet'::text) AND (sportid =3D 'BOX'::text) AND (com= petitionid =3D 'U-1998'::text) AND (eventid =3D 'U-523596':= :text))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Heap Fetches: 0
=C2= =A0Planning Time: 0.221 ms
=C2=A0Execution Time: 0.046 ms

That looks pretty reasonable.
= =C2=A0
1. Now show what happens with the LIMIT clause.
= 2. How many rows does it return?
3. Do you keep the table regular= ly vacuumed and analyzed?

Hey Ron

1. Now show what happens with the LI= MIT clause.
and result set of query=C2=A0 and Size=C2=A0of the= table 287MB
=C2=A0exists
--------
=C2=A0t
(1 = row)
-------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------
=C2=A0Result =C2=A0(cost=3D2.80..2.83 rows=3D1 width=3D1) (actua= l time=3D0.029..0.030 rows=3D1 loops=3D1)
=C2=A0 =C2=A0InitPlan 1 (retur= ns $0)
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan using idx_cacheke= ys on cachekeys =C2=A0(cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time= =3D0.028..0.028 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D = 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (s= portid =3D 'BOX'::text) AND (competitionid =3D 'U-1998'::te= xt) AND (eventid =3D 'U-523596'::text))
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Heap Fetches: 1
=C2=A0Planning Time: 0.084 ms
=C2=A0= Execution Time: 0.043 ms

Th= is might be due to caching.=C2=A0 Run the query with LIMIT three times, and= then remove the LIMIT and run three times.

Honest= ly, though, the execution timings seem pretty good.=C2=A0 What exactly is t= he problem?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'= m still alive.
<Redacted> lobster!
--0000000000005020ba0630453483--