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 1tsusR-006jLs-0e for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:34:23 +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 1tsusP-00CE5a-Hj for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:34:21 +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 1tsusP-00CE5N-6L for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 02:34:21 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsusL-002l5P-2s for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 02:34:20 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-3012a0c8496so2582826a91.2 for ; Thu, 13 Mar 2025 19:34:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741919657; x=1742524457; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=yYPO8w4gc1GaA8g71iaTAqgInIl56bcg+fhDWXiInCk=; b=ifYgiolkdPNBUJo3oS+2wHB6mgK/4mCIxM5pv5zrnfbxOibikFbevEuVw5lfBpiQj2 29cZPBU3PRDFtbZ61J4gsCSnX47Di2lq9ZIT1YOlvroMAx068CGgKiWuLiKIff9YkY4V wKyPyZDbkJMd8mv0j57BxNL68gehlUfFuf4D99wjXX5b+LVFT5fA930pw7KX7UCGDxCl xwri7V0jpukWzjrHDNYrAs50aWWTGkHASzJX5XWwNW/tUEXEgHX9iimjiAv3EhQRhDpJ rdWfkOC1fn5hZWjquDD/6CyyksqrE3KIe8JPC3pqDkDc40Uay/DWJ4rsY/iDsv/wnymJ gfEg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741919657; x=1742524457; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=yYPO8w4gc1GaA8g71iaTAqgInIl56bcg+fhDWXiInCk=; b=wJrAvO4WIBmLX/pSdRr1acgART5fAiGhbckj7XttBLQkioUV7g0a/vZrwpPapI1mDA gdoRkqcUItcLYw8kfWr+5NXrUbjDB7eCk1S+ADAAdKt1j3ldO9w7KG1AmTHN70+k13fS IevMsMZsf1CyoMiFtyxRxJnImR4Tl+iQZSQUBu6jAfDCX94kl6HAkNoRNFce/pdnQh6r WB9louvq+5IEfQ92UliIKaFX8FdHCaSx/WOjEVcTPTfdQEeUiI8TKZ/1upLQQVvSctuN 6S3llHOh9+xf+MeRUFSsCOCxTqHQBWms4Ul2WpT0C7SzU+sifwt6qVG0kXSll6id3Hqz FiGg== X-Forwarded-Encrypted: i=1; AJvYcCUVsQGAJgpTB5lTVfxN4yt8Tl+Ce/Bdl2fiQwv8PccRg/8whUIG1ZIFv5IBHbnFGgixsZl53YQNFWy569d1@lists.postgresql.org X-Gm-Message-State: AOJu0YxwN2++a8KRwpdzLSNRgBCHCjALwZhkzXsuzkeUBrtdYrFhnstL pCXV5IaNdq/q0Fz2unntMLStrW6XuZd82qPHeZQIgMOjjBiay9RL X-Gm-Gg: ASbGncvsP7Y/hkBUPsWc08zR8PGU8257ja69r6KwGTgpYUoigNw+0UGJktGlRPh/J5C sGCcSq0zR8M2VeSGgPEfh6qF7/qWGvd+8UcAaWLCeEjbcippNaQNoRmGtavMR1kBzwSo3ah1tsO ySbWDnFR5JUbHYZFg8eBZ5uddQMERM/FMdl1a2V8hYhZl2Wu3SRxicqGNNRyYrTCPQ7dZZ1YNkI bu+XBexwGf6ufzrRPR646wJ2pIvGrDUH1Y7rw13Z6yBy9eLJH26LiNmjZa3OQvrUapy3rvc+9ag Sreo04kaWr4x7GSOXUiEPCpRKScpeKOuw0gqurSOWGJKGhKiGYEfA7VJG7tft2U+RYNNS8C2Hg= = X-Google-Smtp-Source: AGHT+IH5vz82S7yA1zlCFWeReFfx0ZVY3s0WrW1AJ0ncO+rQYPWA8vuOOSwfAO/N9mH2JjIV5WSTUw== X-Received: by 2002:a17:90b:180f:b0:2ee:f22a:61dd with SMTP id 98e67ed59e1d1-30151dd2784mr971729a91.32.1741919657040; Thu, 13 Mar 2025 19:34:17 -0700 (PDT) Received: from smtpclient.apple ([2601:681:4c01:7310:71ef:ffa6:4033:6c24]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-30153b9953bsm106317a91.37.2025.03.13.19.34.16 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 13 Mar 2025 19:34:16 -0700 (PDT) From: Rob Sargent Message-Id: <77CFD347-1A36-4A78-8D95-21569977C0A5@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_20FF06F3-DEE5-4741-A54A-1D3E3A16FAB6" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.15\)) Subject: Re: Query optimization Date: Thu, 13 Mar 2025 20:34:15 -0600 In-Reply-To: Cc: Adrian Klaver , pgsql-general , laurenz.albe@cybertec.at To: Durgamahesh Manne References: X-Mailer: Apple Mail (2.3654.120.0.1.15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_20FF06F3-DEE5-4741-A54A-1D3E3A16FAB6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >=20 > 3) Output of EXPLAIN ANALYZE of query. >=20 > 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 >=20 And is the explain analyze output if from your original query: "SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType =3D $1 AND = TrsId =3D $2 AND BrandId =3D $3 AND SportId =3D $4 AND CompetitionId =3D = $5 AND EventId =3D $6 AND MarketId =3D $7 LIMIT ?)=E2=80=9D Has there been any significant addition of rows recently? i.e. are the = statistics up-to-date for that table? --Apple-Mail=_20FF06F3-DEE5-4741-A54A-1D3E3A16FAB6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8


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



And is the explain analyze = output if from your original query:
"SELECT EXISTS (SELECT Key FROM CACHEKEYS = WHERE CacheType =3D $1 AND TrsId =3D $2 AND BrandId =3D $3 AND SportId =3D= $4 AND CompetitionId =3D $5 AND EventId =3D $6 AND MarketId =3D $7 = LIMIT ?)=E2=80=9D

Has there been any = significant addition of rows recently? i.e. are the statistics = up-to-date for that table?


= --Apple-Mail=_20FF06F3-DEE5-4741-A54A-1D3E3A16FAB6--