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 1tsvA7-006mCc-Fy for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:52:39 +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 1tsvA6-00Cmrx-3N for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:52:38 +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 1tsv6v-00CgFp-ON for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 02:49:21 +0000 Received: from mail-oo1-xc32.google.com ([2607:f8b0:4864:20::c32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsv6s-002icf-15 for pgsql-general@postgresql.org; Fri, 14 Mar 2025 02:49:19 +0000 Received: by mail-oo1-xc32.google.com with SMTP id 006d021491bc7-601b1132110so739071eaf.3 for ; Thu, 13 Mar 2025 19:49:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741920557; x=1742525357; 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=s6E27kDEoAG7CFxoEBjzKQsz9D/IsRpsvVvcnIXaZs4=; b=T1YF8onidHsJav4j7D29lJfnRipv1KTVcOnfVhxYj3o0AcYmqpydVNOPLt44y65sPQ 77chaKjJoKUktu+J4SrJVUIseUIUFzxakLc4MkBw+Ob+YssQzQ09nXIvSdMSwrXkCVUl ktFWphIQPg6GQ8Zt7HzOm4MCXzQw+jtcYKeUCB8EfI+d+EL0EmCrN8tSZGHL30MrmVbS 1Vt6i6Q/6BGEUqbefDvD7F56dsd8mEy1a88J9TfeaB/ksdNsxc0dGi1x6SJzCNyDLS8x 9ptLWkGmTJ+angf8mft5n4wndToU2HJiHH1qGaEgWaDzn1TDCwUzg0cHTzHQSy+oq8ZQ OrZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741920557; x=1742525357; 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=s6E27kDEoAG7CFxoEBjzKQsz9D/IsRpsvVvcnIXaZs4=; b=bV+vdmLfxQ0/vSfTaM1F+cFK8LYs0ZQ09878Nh1riBqPQZ0RvXWYPgW6g/3HIeZO0g 4nBhulA7F14XrVwOF4Ufv7zRTb6l+yRojyhrNRj3cn9EArs0QFGZbXtYAV3f3h6Y7tG3 N5ZG4RMDPbCs30dNTjsD2niQ5mmYDC4gouVzXFaSpFEh/S6w93j1j1NZynLPJZiUoKZf sioj4usuznqA+Ykies1VsnfnUAgFNJvoJh09DsfpK8n8uJayJ22jV3Vw+cKm/Pm8OrOJ gIhlYJOstES9rEn+XP0fILd1G5Se3Ln70hMxKRNcGwT3tbfiaES5Xi+uNRM9vaNr7iaA bUBQ== X-Gm-Message-State: AOJu0YwdVST4xC0suXi0hMRyQjMyRMBncHNEk10GWljpOnsp1EnkIAbA /XVGOQ5MnWw8ENAj+Ar63Tuy4o8mDZRd4RhmJCM053Rzg1mqXO5KH84ulSLh0UVIhlQQnOzFRcS CiLhfkXkKGGqQVEdFBhbwehv28gpPVA== X-Gm-Gg: ASbGncv5+WjgXwGd1nVEDQac/45CHv/q8KYTsU7IVvXcZQn4F6cLWsrxHViowY5OCRZ uaWBnAay4WM0kay06qDiw8vRgylFArNmoq10y/NDtt+Bh3HFJ/T2ZP6tTz1/BSrCvwcoECzP6jM IQPv+0Rsm4X+WiFFsFTgsHTm+W3nmfXrZZC8VC4SO5ZXQ7xx0BdM0poGxzw3XU X-Google-Smtp-Source: AGHT+IHMBgCkIVG6OYswcVoEq9e5uh3xvMZ9Bb3lavJ1hAEIvshVD+uos3IyEfcOjvm1dB7jefmdSW+sSK8As2S0PK8= X-Received: by 2002:a05:6820:202:b0:601:a6ec:881c with SMTP id 006d021491bc7-601e456e04emr575024eaf.1.1741920557178; Thu, 13 Mar 2025 19:49:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 13 Mar 2025 22:49:06 -0400 X-Gm-Features: AQ5f1JpSK7TEzbvt7TuzxxkDLbfp_-Nk41Oq_Rh0be4kSWk0-edfe3IFUy3ZeFQ Message-ID: Subject: Re: Query optimization To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000009a7a70630447c65" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000009a7a70630447c65 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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.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.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? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000009a7a70630447c65 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Mar 13, 2025 at 10:16=E2=80=AFPM = Durgamahesh Manne <maheshpo= stgres9@gmail.com> wrote:
[snip]=C2=A0
Hi A= drian Klaver

1) Postgres version.
=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 v= ersion
-----------------------------------------------------------------= ----------------------------------------------
=C2=A0PostgreSQL 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.

= =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.cachekeys"
=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=A0cachetype =C2=A0 =C2=A0 | ch= aracter 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=A0brandid =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=A0sportid =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=A0competitionid | 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 | 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=A0keytype =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=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" PRIMARY KEY, btree (key)
=C2=A0 = =C2=A0 "idx_cachekeys" btree (cachetype, 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) (act= ual time=3D0.030..0.030 rows=3D1 loops=3D1)
=C2=A0 =C2=A0InitPlan 1 (ret= urns $0)
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan using idx_cache= keys on cachekeys =C2=A0(cost=3D0.55..2.80 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 '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: 0
=C2=A0Planning Time: 0.221 ms
=C2=A0= Execution Time: 0.046 ms

Th= at looks pretty reasonable.
=C2=A0
1. Now show what hap= pens with the LIMIT clause.
2. How many rows does it return?
3. Do you keep the table regularly vacuumed and analyzed?
<= br>
--
Death to <Redacted&= gt;, and butter sauce.
Don't boil me, I'm still alive.
=
<Redacted> lobster!
--00000000000009a7a70630447c65--