Skip to content

Pg-Mem returns incomplete data when index has a where clauses that doesn't capture it #458

@rswheeldon

Description

@rswheeldon

Describe the bug

If I have a partial index (for performance) but look for values which don't use that index, postgres still finds them but pg-mem doesn't.

To Reproduce

First set up a table and an index - this all works fine.

postgres=# create table foo(x integer, y integer, z integer);
CREATE TABLE
postgres=# create index foox1 on foo(y) where x = 1;
CREATE INDEX
postgres=# insert into foo values (1,2,3), (1,1,1), (2,3,5);
INSERT 0 3
postgres=# select * from foo;
 x | y | z 
---+---+---
 1 | 2 | 3
 1 | 1 | 1
 2 | 3 | 5
(3 rows)

postgres=# select * from foo where y = 1;
 x | y | z 
---+---+---
 1 | 1 | 1
(1 row)

Now add an additional row which falls outsides the index:

insert into foo values (4,1,4);
select * from foo where y = 1;

The index can't be used, hence why the postgres plan is this:

postgres=# explain select * from foo where y = 1;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on foo  (cost=0.00..35.50 rows=10 width=12)
   Filter: (y = 1)
(2 rows)

But (presumably) pg-mem still tries to use the index which is incomplete and thus returns the wrong data, namely:

Image

pg-mem version

3.0.5 and current playground

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions