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 1tsvgC-006rkI-6g for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:25:48 +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 1tsvg9-00DOUu-Gp for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:25:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsvg9-00DOUh-1T for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:25:45 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsvg5-002lRN-0I for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:25:44 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-ac298c8fa50so312592366b.1 for ; Thu, 13 Mar 2025 20:25:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741922741; x=1742527541; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=dkH0CwiOk2urg1gXEuo9Zkv5Ovqx4mrW/PP307HsxC8=; b=QJweAXyKQL55PnMtdfKrrX4bKh8E89Jor8r3R7kh5+38wTb6gJ8rrQ4O8iT4m/qRj+ 7TPuMC0Mgzb1QvlJs1/qWq15KwcJv9KBisbDgbxoVePBtUbq4+cfjmfbP2T5k+Mw8xYM teTETie+KyH86Px+uV8GpkC1Ytir9brzxax3f+8h8tUrEzCvkTLoRZfB5+fgSxEAKKZm ZiKirN3eTU1m4oH6FlS8yzVWZCdEvZLPchGHvXoZUOKldWDs1cSL6yXVlTJy4qw50qvW fj6LkgIJwPTfN+oEN/07DfX8pmfxGMoh/g3M7KOuKTsvEjQMOi4F9AIhIOWFFNbRWCDV mAow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741922741; x=1742527541; h=cc: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=dkH0CwiOk2urg1gXEuo9Zkv5Ovqx4mrW/PP307HsxC8=; b=WYY+mFQ6nea6VmRZo/PXk3ukaYhNxwkfM1mpd/qFxvt+I/wIeYwVSU3am70KrxRz1u XkjKYWG1jTp1UybIoPIruvM5Y8ZOYuLpJA/nXc2VPXQ1rWwvmTxkb4sfY06fCQL0KEWV ii5iu21RFn4UrKnPnBSnYWYaoZ30P85VkZ3fXw661W3K1n81+yDzUcKslWfENXcnLD6O 1Q4K6aNojHA0OwJFKnftJZ5b+308uDxI+eJKPnh5aJTiOa2LYC9EmZGyPkz7Sn/m1Jcl BdeNYuDz8YAYpxUg4HVz59uJsQevOpO3eq4+0sRoXKrNcsL6ovnrylTs5fdxobMYqXsd PTYg== X-Forwarded-Encrypted: i=1; AJvYcCWE21SN978K2lDLp/b6XJhf+h2envYpDXYTSYU5fzgxuKbyr55MzMvYCPy5DQrQrxZEdIzxXE6jkzA/nk8a@lists.postgresql.org X-Gm-Message-State: AOJu0YyOXW6d1+aZFnz+VtPFb+T/0A74dHme3XSaghJOP0DPd3Gto7yx 9nxdbrhvpaVdKn/VbkinmRcqkApQISZDtrX1kYtRjo9YwWORoq/D/jXtAXIzbeJA7I+UKP6SAvV OoObmOcp2z0t59Ur9bxVvAC8ICzQ= X-Gm-Gg: ASbGnctV3bO1ny5mII3r7uLf0V2mmM0IP6oBep7MN1O//myT6F7bONXNtogXsc6CuZ4 AYWTKfHC64eaX98ei7CGoJqCIImd52H5dfVLmmpW6xaNlMTuiIb3GfPNIBFAVIPGu4r0ypWVdB+ A1fVd5ON2WwYjS6+Yt+Ug/brgo9A== X-Google-Smtp-Source: AGHT+IEI8gQSojzJmY5mxNwfjGZgBHfNPXyz2q66AE+ShdclIHOuGZEU/l3ix7XoSo40WQ0v1huvR9/5C+4WKDkHAmg= X-Received: by 2002:a17:906:4795:b0:ac1:dfab:d397 with SMTP id a640c23a62f3a-ac33026ab71mr81113166b.21.1741922740876; Thu, 13 Mar 2025 20:25:40 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Fri, 14 Mar 2025 08:57:41 +0530 X-Gm-Features: AQ5f1JoO7_kqE1Nx-PTabJfti82H591JG0OADdEcj8Jwffu5arUeDpk8SSojNCg Message-ID: Subject: Re: Query optimization To: Ron Johnson Cc: pgsql-general , pgsql-general Content-Type: multipart/alternative; boundary="000000000000323d07063044fe18" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000323d07063044fe18 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 | Default >> | 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 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: 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? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > 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.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.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 (7 rows) 2. How many rows does it return? One row exists -------- t (1 row) 3. Do you keep the table regularly vacuumed and analyzed? Auto vacuum already in place along with periodic maintenance activity such as vacuum and analyze runs daily once Regards, Durga Mahesh --000000000000323d07063044fe18 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, Mar 14,= 2025 at 8:19=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, = Mar 13, 2025 at 10:16=E2=80=AFPM Durgamahesh Manne <maheshpostgres9@gmail.com>= ; wrote:
[snip]=C2=A0
Hi Adrian Klaver

1) Postgres ve= rsion.
=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?

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


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
(7 rows)

=
2. How many rows does it return?
One row=C2=A0 exists
-= -------
=C2=A0t
(1 row)
3. Do you keep the table regularly vacuum= ed and analyzed?
Auto vacuum already in place along with periodic= maintenance activity such as vacuum and analyze runs daily once=C2=A0

Regards,
Durga Mahesh
=C2=A0
--000000000000323d07063044fe18--