search

hydradatabase / hydra

Hydra: Column-oriented Postgres. Add scalable analytics to your project in minutes.
https://www.hydra.so
Apache License 2.0
2.8k stars 73 forks source link

[Bug]: Having index with "char"/char(1)/text column with only 1 character when used causes data falsely to not be returned #217

Closed muntdan closed 8 months ago

muntdan commented 8 months ago

What's wrong?


with CTE as ( select a,b,targetid from T1 where datetime between '2023-12-20 05:00' and '2023-12-20 06:00' )
SELECT * from CTE left join LATERAL 
(Select d,e,targetid  from T2 where (datetime > CTE .datetime - interval '1 Hour' and datetime <= CTE.datetime )
        and targetid = CTE.targetId  
            order by DateTime desc  Limit 1) t ON true;`

The     line:   and targetid = CTE.targetId  makes the LATERAL to not work and return nothing
Also on the CTE if you add  at the end order by datetime nothing is returned
JerrySievert commented 8 months ago

can you please check SELECT current_setting('enable_memoize');?

muntdan commented 8 months ago

image

JerrySievert commented 8 months ago

can you provide a self-contained reproduction of the issue you are seeing?

muntdan commented 8 months ago

Ok so now I narrowed it down to the more where conditions that sometime cause no result: image image

I should mention: I use PG 16 docker and that tenant column is a partition column.

JerrySievert commented 8 months ago

there are several issues with the char type in postgres, and probably shouldn't be used - are you also using it for the field type?

muntdan commented 8 months ago

Which field type ?

JerrySievert commented 8 months ago

the char field type, which you are doing a conversion to for comparison of tenant.

muntdan commented 8 months ago

Yes the column in db is type "char".

And this one works: select * from event where targetid =69325 and tenant::text='4' and datetime between '2023-12-20 04:00' and '2023-12-20 08:05'

muntdan commented 8 months ago

But because when using "char" its selecting the correct partition I suspect the issue with "char" is only on Hydra image

JerrySievert commented 8 months ago

I would recommend creating tables with a different type (TEXT, VARCHAR, or whatever works other than CHAR) and try with that - you should not need to cast, and you should find it works a bit better.

JerrySievert commented 8 months ago

CHAR types are an issue in heap as well as columnar. they do not work like you would expect them to, and have issues with conversions. tenant::text works because it strips the additional white spaces added in the CHAR type that are invisible to you.

muntdan commented 8 months ago

I've been running Timescaledb for > 1 year with secondary dimension the same tenant "char" and had no issues so far but will surely switch on hydra where the storage is a lot smaller and regular char will not have an impact

muntdan commented 8 months ago

I spend all day redoing the database with tenant column as char(1) instead of "char" and surprise the bug is the same:

This 2 work: image

This 2 dont work: image

JerrySievert commented 8 months ago

your pictures are still showing tenant as a char.

muntdan commented 8 months ago

Yes but now is standard char(1) not the legacy "char"

JerrySievert commented 8 months ago

as noted, that will not fix the issue.

muntdan commented 8 months ago

Ok I re-made the database and remigrated all again with column text. Same bug:

This works: image

Explain json:

"[ { ""Plan"": { ""Node Type"": ""Gather"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 139, ""Actual Loops"": 1, ""Workers Planned"": 4, ""Workers Launched"": 4, ""Single Copy"": false, ""Plans"": [ { ""Node Type"": ""Append"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": true, ""Async Capable"": false, ""Actual Rows"": 28, ""Actual Loops"": 5, ""Workers"": [ ], ""Subplans Removed"": 0, ""Plans"": [ { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten7_2023_42"", ""Alias"": ""event_8"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten6_2023_42"", ""Alias"": ""event_7"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten0_2023_42"", ""Alias"": ""event_1"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten3_2023_42"", ""Alias"": ""event_4"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten4_2023_42"", ""Alias"": ""event_5"", ""Actual Rows"": 139, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 24, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 7, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten2_2023_42"", ""Alias"": ""event_3"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 5, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten5_2023_42"", ""Alias"": ""event_6"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 4, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten1_2023_42"", ""Alias"": ""event_2"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 4, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten8_2023_42"", ""Alias"": ""event_9"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten9_2023_42"", ""Alias"": ""event_10"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] } ] } ] }, ""Triggers"": [ ] } ]"

All this dont work: image

Explain json:

"[ { ""Plan"": { ""Node Type"": ""Index Scan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Scan Direction"": ""Forward"", ""Index Name"": ""event_ten4_2023_42_tenant_datetime_targetid_idx"", ""Relation Name"": ""event_ten4_2023_42"", ""Alias"": ""event"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Index Cond"": ""((tenant = '4'::text) AND (datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Rows Removed by Index Recheck"": 0 }, ""Triggers"": [ ] } ]"

And this somehow works: image

Explain json:

"[ { ""Plan"": { ""Node Type"": ""Gather"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 139, ""Actual Loops"": 1, ""Workers Planned"": 4, ""Workers Launched"": 4, ""Single Copy"": false, ""Plans"": [ { ""Node Type"": ""Append"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": true, ""Async Capable"": false, ""Actual Rows"": 28, ""Actual Loops"": 5, ""Workers"": [ ], ""Subplans Removed"": 0, ""Plans"": [ { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten7_2023_42"", ""Alias"": ""event_8"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten6_2023_42"", ""Alias"": ""event_7"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten0_2023_42"", ""Alias"": ""event_1"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten3_2023_42"", ""Alias"": ""event_4"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten4_2023_42"", ""Alias"": ""event_5"", ""Actual Rows"": 139, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 24, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 7, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten2_2023_42"", ""Alias"": ""event_3"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 5, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten5_2023_42"", ""Alias"": ""event_6"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 4, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten1_2023_42"", ""Alias"": ""event_2"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone))"", ""Columnar Chunk Groups Removed by Filter"": 4, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten8_2023_42"", ""Alias"": ""event_9"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] }, { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Member"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Relation Name"": ""event_ten9_2023_42"", ""Alias"": ""event_10"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Filter"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND ((tenant)::integer = 4))"", ""Rows Removed by Filter"": 0, ""Columnar Projected Columns"": ""eventid, datetime, tenant, targetid, eventtypeid, data"", ""Columnar Chunk Group Filters"": ""((datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Columnar Vectorized Filter"": ""(targetid = 44722)"", ""Workers"": [ ] } ] } ] }, ""Triggers"": [ ] } ]"

The Event table has 14.455.064 rows and this index: CREATE INDEX IF NOT EXISTS event_tenant_datetime_targetid_idx ON public.event USING btree (tenant ASC NULLS LAST, datetime DESC NULLS LAST, targetid ASC NULLS LAST); If I drop the index the query starts working.

Mind blowing same index and same queries but on another table with 225.882.726 rows works in all combinations.

muntdan commented 8 months ago

So you believe the index causing no data return is related to vacuum?

wuputah commented 8 months ago

Based on the (deleted) comment history it wasn't clear that this issue was still current. If you can provide a reproducible test case we would be happy to look into it.

muntdan commented 8 months ago

Unfortunately all my attempts to create scripts that generate data to reproduce the issue were not successful. Only on my real db data this reproduces and I did the migration at least 7 times so is consistent. Once I drop the index the results start coming and the excution plan no longer looks like this: "[ { ""Plan"": { ""Node Type"": ""Index Scan"", ""Parallel Aware"": false, ""Async Capable"": false, ""Scan Direction"": ""Forward"", ""Index Name"": ""event_ten4_2023_42_tenant_datetime_targetid_idx"", ""Relation Name"": ""event_ten4_2023_42"", ""Alias"": ""event"", ""Actual Rows"": 0, ""Actual Loops"": 1, ""Index Cond"": ""((tenant = '4'::text) AND (datetime >= '2023-10-20 04:00:00'::timestamp without time zone) AND (datetime <= '2023-10-20 08:05:00'::timestamp without time zone) AND (targetid = 44722))"", ""Rows Removed by Index Recheck"": 0 }, ""Triggers"": [ ] } ]"