Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

ould zone-in in the source code to look for hints, maybe ? Appreciate it ! Danny On Wed, Nov 23, 2022 at 4:29 PM Tom Lane wrote: > Danny Shemesh writes: > > -> Index Only Scan using > > idx_hashes on refs (cost=0.56..722735.47 rows

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

Fetches: 10 Buffers: shared hit=90020 read=164 I/O Timings: read=151.151 Planning: Buffers: shared hit=8 Planning Time: 0.505 ms Execution Time: 297.948 ms I can also note that if I change the IN expressi

Index filter instead of index condition w/ IN / ANY queries above certain set size

Hey everyone, I'm trying to understand when the planner decides to use an index condition vs an index filter, specifically for x IN / = ANY {set}, and if we can tune some parameters to move it between these plans. We have two tables and a query similar to the following fiddle: https://www.db-fidd

Expr. extended stats are skipped with equality operator

Hey all ! I'm on a quest to help the planner (on pg14) use the best of several partial, expressional indices we have on some large tables (few TBs in size, billions of records). As we know, stats for expressions in partial indices aren't gathered by default - so I'm tinkering with expressional ex

Re: Index only scans for expressional indices when querying for the expression

worth the trade-off ? Thanks again, Danny On Thu, Aug 4, 2022 at 4:38 PM Tom Lane wrote: > Danny Shemesh writes: > > That is of course correct, but what I mean is that, I think that if one > > would explicitly query f(x), and never for x directly, it would've been > &g

Re: Index only scans for expressional indices when querying for the expression

ugust 4, 2022, Danny Shemesh wrote: >> >> I believe the expressional index in itself could've been considered as >> covering, when querying for the expression explicitly. >> > > This belief is wrong. When storing f(x) there is no way to recover the > value of x. > > David J. > >

Index only scans for expressional indices when querying for the expression

Hello everyone, Quick question here about index-only scans and expressional indices, tested on postgres <= 14, Say I have a column, and an expressional index on said column (fiddle ) - e.g. create table t1 (x text); create index idx_upper o

Re: Extended multivariate statistics are ignored (potentially related to high null fraction, not sure)

, I'd really appreciate hearing them. Thanks again ! On Wed, Jun 1, 2022 at 11:08 PM Bruce Momjian wrote: > On Wed, Jun 1, 2022 at 07:28:58PM +0300, Danny Shemesh wrote: > > Hey everyone, > > > > I'm working on improving gathered statistics on several large tables

Extended multivariate statistics are ignored (potentially related to high null fraction, not sure)

Hey everyone, I'm working on improving gathered statistics on several large tables (2TB / 500M records); I've created extended stats on correlated columns, ran analyze, compared the pre and post explains, and it seems as though the extended statistics are ignored - the estimation doesn't change mu

The use of partial, expressional indices in pg < 14

Hey everyone, There's something I've been wondering about - to my understanding, the planner won't use statistics collected on partial indices, as they may or may not reflect the correct distribution of data. When using expressional indices which are also partial, a-la an index on a nested path o

Showing alternative query planner plans with explain ?

Hey all ! I'm currently optimizing queries and indices on a relatively large dataset; one of the frequent questions I seem to ask myself is why the planner chooses plan A over B. Reading the docs, blogs, stack exchange posts, wiki, ... helps in trying to tinker with the query or indices in a way

Would it be possible to utilize a GIN index to query for distinct values ?

Hey everyone ! Bumping an older thread , I've read the GIN readme in the code base and have skimmed through the implementation, it made me wonder - would it be possible to use the ind