Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxqgE-0002FP-Uy for pgsql-general@arkaria.postgresql.org; Wed, 23 Nov 2022 14:24:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxqgD-0004Iu-JB for pgsql-general@arkaria.postgresql.org; Wed, 23 Nov 2022 14:24:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxqgD-0004Il-8F for pgsql-general@lists.postgresql.org; Wed, 23 Nov 2022 14:24:49 +0000 Received: from mail-wr1-x429.google.com ([2a00:1450:4864:20::429]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oxqg6-0007Kv-VK for pgsql-general@postgresql.org; Wed, 23 Nov 2022 14:24:48 +0000 Received: by mail-wr1-x429.google.com with SMTP id x5so25568380wrt.7 for ; Wed, 23 Nov 2022 06:24:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=PIo5I9A3W88Zc6Aytygs5jdCE+HAw6YIVljuAybKVbM=; b=KY7DhiVsgnO/VYZ9Pq25D8AkFmDHg8NBETJC4ZFV1/7uS68EZJKQBSNEN5OKFuhGS5 bKBK/5rLU5Vm1pBsiGUBzHCJFqdMpxSv89YncCPIpePkk8gc7RoF0nZOPHIk4AB6od0i vLbGdMzEnZ5pYmVE/adEL1avSXwPHc215yU4P4lDhqanK9ONTtV7aWMbjkJdbVO77zZ9 dVobYdJn/mqIB63Tw0NXBE2YNiTQnk61FizUQK7SK0m0bhsTAbAtXWIOKxBM65KEXzB+ BUZt1d6RP0bpgnVIgNOhl5vPJyQNfsio+zcX20BsL+LMAwy7kLZzqWaI2kSFoudQ7rRG HCpQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=PIo5I9A3W88Zc6Aytygs5jdCE+HAw6YIVljuAybKVbM=; b=adDSfna/1Fy+TmeFNY81wI3yniR+5+sDJb2sbnMqAsm6W5MoBXW5jDJQMJNGuHKf+6 ley2vQOq39WLN+ZY8zOy/El+Fq84X1YMkZcEE056mNGKu4CpIeH4zJF0MttlZAo/56DU a8N11A1GQuduyiil4USHOWxEcvUNIrwM4FnoMjdO+tftsivrimU54SeKAlFKMBogN++S s/ohvoabaE1g56en8qaMlYosOKa66k0/v+5fiDDz1GYR4vjPafP7ftWf5tHahAH6PZr9 UFuqDseSTL0zqw8hUV1keocqIQg+K63xzEcjnkS4B+Y/rqUexXUsDP/CovdUo4zdYL0+ 7NeQ== X-Gm-Message-State: ANoB5pkCjYDMIRZx1ftJmclyxy2hTjOOa2U0qBgKw9MsyaQPZe0UAPIJ pN+5xRx5h4IPk6u0Sg5yE65L2w== X-Google-Smtp-Source: AA0mqf4rmi19ba27bRLKAGG7wJV4K5Tuj7YE+LGdUfDeBTPwe1x/9bW26yMlACfERjOyhDMZqHFQQA== X-Received: by 2002:a05:6000:235:b0:241:ebff:2db1 with SMTP id l21-20020a056000023500b00241ebff2db1mr2181101wrz.312.1669213481554; Wed, 23 Nov 2022 06:24:41 -0800 (PST) Received: from localhost.localdomain (91-115-151-172.adsl.highway.telekom.at. [91.115.151.172]) by smtp.gmail.com with ESMTPSA id n23-20020a05600c3b9700b003cfa81e2eb4sm2892850wms.38.2022.11.23.06.24.41 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 23 Nov 2022 06:24:41 -0800 (PST) Message-ID: <73d1582fec0ef31e1f2e072e979a875cc851eff6.camel@cybertec.at> Subject: Re: Index filter instead of index condition w/ IN / ANY queries above certain set size From: Laurenz Albe To: Danny Shemesh Cc: pgsql-general@postgresql.org Date: Wed, 23 Nov 2022 15:24:40 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2022-11-23 at 15:38 +0200, Danny Shemesh wrote: > ->  Limit  (cost=0.56..24.17 rows=1 width=16) (actual time=2657.167..2657.167 rows=1 loops=39) >       Buffers: shared hit=323452 read=887661 >       I/O Timings: read=2369932.536 >       ->  Index Only Scan using idx_hashes on refs refs_1  (cost=0.56..265306.68 rows=11238 width=16) (actual time=2657.162..2657.162 rows=1 loops=39) >             Index Cond: ((tid = '13371337-1337-1337-1337-133713371337'::uuid) AND (pidh > cte_1.pidh)) >             Filter: (tidh = ANY ('{13391339-1339-1339-1339-133913391339}'::uuid[]))    <<<<<<<<<<<<<<<- Note this line >             Rows Removed by Filter: 346024 >             Heap Fetches: 1506359 >           Buffers: shared hit=323452 read=887661 >             I/O Timings: read=2369932.536 PostgreSQL thinks that there are enough such rows that it is cheaper to use the index that supports the ORDER BY. I don't know why there is a difference between = ANY and = here, but you can use an expression like "ORDER BY pidh + 0" to avoid that. Yours, Laurenz Albe