# Query using MAX on column using calculated partition key on that column not performing as expected

**URL:** <https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918>\
**Category:** CrateDB\
**Created:** [December 4, 2024, 11:31am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918 "2024-12-04T11:31:09Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 4, 2024, 11:31am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/1 "2024-12-04T11:31:09Z")

</div>

I have a table with billions of records, that looks something like:

```auto
CREATE TABLE IF NOT EXISTS "public"."locations" (
   "last_response" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   "id" BIGINT DEFAULT CAST(current_timestamp AS bigint) NOT NULL,
   
   --details elided
   
   "part_hour" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('hour', "last_response"),
   "part_day" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('day', "last_response"),
   "part_week" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('week', "last_response") NOT NULL,
   "part_month" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('month', "last_response"),
   PRIMARY KEY ("id", "part_week")
)
CLUSTERED INTO 6 SHARDS
PARTITIONED BY ("part_week")

```

This query

```auto
SELECT MAX(last_response) from public.locations WHERE last_response >= '2021-01-23 00:00:00.000000' AND last_response < '2022-01-01 00:00:00.000000';

```

The EXPLAIN is:

```auto

HashAggregate[max(last_response)] (rows=1)
  + Collect[public.locations | [last_response] | ((last_response >= 1611360000000::bigint) AND (last_response < 1640995200000::bigint))] (rows=0)

```

and takes a long time (more than a minute) to complete.

As the table is partitioned on part\_week, which is in turn based on last\_response, I would have expected the logic to be “identify shard with highest part\_week, return max(last\_response) from that”, and it should return nearly instantly.

Where is my understanding flawed please?

With many thanks.

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 4, 2024, 3:12pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/2 "2024-12-04T15:12:08Z")

</div>

Hi,  
Could you try adding this to your `WHERE` clause?

```sql
AND last_response>= (SELECT MAX(a.part_week) FROM public.locations a)

```

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 4, 2024, 3:28pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/3 "2024-12-04T15:28:46Z")

</div>

It took half as long, and interestingly returned NULL result.

The explain is

```auto
MultiPhase (rows=1)
  └ HashAggregate[max(last_response)] (rows=1)
    └ Collect[public.locations | [last_response] | (((last_response >= 1611360000000::bigint) AND (last_response < 1640995200000::bigint)) AND (last_response >= (SELECT max(part_week) FROM (a))))] (rows=0)
  └ Limit[2::bigint;0::bigint] (rows=1)
    └ HashAggregate[max(part_week)] (rows=1)
      └ Rename[part_week] AS a (rows=7845405573)
        └ Collect[public.locations | [part_week AS date_trunc('week', last_response)] | true] (rows=7845405573)

```

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 4, 2024, 3:38pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/4 "2024-12-04T15:38:32Z")

</div>

Sorry, my mistake, try

```sql
AND last_response>= (
   SELECT MAX(a.part_week) 
   FROM public.locations a
   WHERE a.part_week <= date_trunc('week','2022-01-01 00:00:00.000000')
   )

```

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 4, 2024, 3:55pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/5 "2024-12-04T15:55:09Z")

</div>

Thanks very much, but no change.

```auto
MultiPhase (rows=1)
  └ Limit[100::bigint;0] (rows=1)
    └ HashAggregate[max(last_response)] (rows=1)
      └ Collect[public.locations | [last_response] | (((last_response >= 1611360000000::bigint) AND (last_response < 1640995200000::bigint)) AND (last_response >= (SELECT max(part_week) FROM (a))))] (rows=0)
  └ Limit[2::bigint;0::bigint] (rows=1)
    └ HashAggregate[max(part_week)] (rows=1)
      └ Rename[part_week] AS a (rows=7079955502)
        └ Collect[public.locations | [part_week AS date_trunc('week', last_response)] | (part_week <= 1640563200000::bigint)] (rows=7079955502)

```

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 4, 2024, 3:59pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/6 "2024-12-04T15:59:32Z")

</div>

What about this?

```sql
AND last_response>= (
   SELECT MAX(values['part_week']) 
   FROM information_schema.table_partitions 
   WHERE values['part_week'] <= date_trunc('week','2022-01-01 00:00:00.000000')
      AND table_name='locations' 
      AND table_schema='public'
   )

```

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 4, 2024, 4:03pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/7 "2024-12-04T16:03:47Z")

</div>

That returned a value, but no quicker than without the new clause.

```auto
MultiPhase (rows=1)
  └ Limit[100::bigint;0] (rows=1)
    └ HashAggregate[max(last_response)] (rows=1)
      └ Collect[public.locations | [last_response] | (((last_response >= 1611360000000::bigint) AND (last_response < 1640995200000::bigint)) AND (last_response >= (SELECT max(values['part_week']) FROM (information_schema.table_partitions))))] (rows=0)
  └ Limit[2::bigint;0::bigint] (rows=1)
    └ HashAggregate[max(values['part_week'])] (rows=1)
      └ Collect[information_schema.table_partitions | [values['part_week']] | (((values['part_week'] <= 1640563200000::bigint) AND (table_name = 'locations')) AND (table_schema = 'public'))] (rows=unknown)

```

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 4, 2024, 4:06pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/8 "2024-12-04T16:06:24Z")

</div>

Maybe do one last test changing `last_response>= (` with `part_week = (`  
and if that does not help please share the output of `EXPLAIN ANALYZE`

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 4, 2024, 4:25pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/9 "2024-12-04T16:25:40Z")

</div>

[analyze.txt](https://community.cratedb.com/uploads/short-url/bWyVqngsffeRCr0OKlI3M1Nj7IB.txt) (1.3 MB)

Please find attached. Many thanks

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 10, 2024, 8:44am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/10 "2024-12-10T08:44:19Z")

</div>

Hi there.

Thanks so much for your help thus far.

Appreciate that the ANALYZE output is super verbose. But I suspect the bottom line (with my inexperienced eye) is that every partition is indeed being considered.

I wonder if you could advise if my underlying assumption (that the partition key being one that is calculated from a given column, means that queries on that original column should in theory eliminate partitions) seems correct on the face of it?

Or would you always expect additional clauses to be needed if its a calculated column?

(I’m trying to more or less lift and shift existing postgres app, so changing queries at all is undesirable)

Thanks again.

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 10, 2024, 3:31pm UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/11 "2024-12-10T15:31:51Z")

</div>

Hi,  
The 3 execution plans you shared show indeed multiple `PartitionIdent` being considered.  
However on my repro environment on CrateDB 5.9.4 , the query goes straight to the right partition, even without introducing the `part_week =` clause .

> would you always expect additional clauses to be needed if its a calculated column?

No, actually the additional clause should not be needed, see for instance [the example on this GitHub ticket closed a number of months ago](https://github.com/crate/crate/issues/15667).

I understand you may be on 5.9 but maybe not on 5.9.4?

I see your original execution plan has `(rows=0)` but then you were surprised to receive a NULL result, this may point to stale statistics or [left-behind partitions following the deletion of some records](https://github.com/crate/crate/issues/12158). Do you have [statistics collection disabled](https://cratedb.com/docs/crate/reference/en/latest/sql/statements/analyze.html) by any chance?

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 11, 2024, 11:02am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/12 "2024-12-11T11:02:22Z")

</div>

Thanks for coming back to me.

Upgraded from 5.9.3 to 5.9.4, but no change.

My config is pretty much default. So it was the default stats values, including enabled. I set it to collect stats every 10 mins just in case, but no change. Is there a more definitive way to be very sure the stats is indeed collected?

I have never deleted any rows from this table.

Experimenting with reducing the range in the query - so that it’s looking at a narrow range of last\_response - the query time come down seemingly linearly, and the ANALYZE shows that it is correctly only considering partitions in that range.

But each of the MAX queries on each of those partitions is taking a very long time - about a second. The partitions are largish - 50 gig - with 212m records say. Is that duration expected? Or should the indexing of this TIMESTAMP WITHOUT TIME ZONE field be able to return the max immediately? If so, then that is perhaps the root problem here?

 ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/b/b5657a1f20576de084e4a0cd1e6422d3bfe79b8c.png)

Thanks again.

---

<div class="post-metadata">

**Author:** ![ness](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/ness/32/1292_2.png) [@ness](https://community.cratedb.com/u/ness)\
**Post date:** [December 18, 2024, 10:49am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/13 "2024-12-18T10:49:06Z")

</div>

Appreciate if anyone is able to comment on my last query. It feels like something is broken regarding indexing - I would presume that queries like MAX(X) where X is a timestamp should be immediately satisfied for a shard in almost no time. Is that in fact not the case?

Are there any tips as to how I can proceed with diagnosing the root cause? I’m very reluctant to give up on Crate.

Thanks very much.

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [December 18, 2024, 11:03am UTC](https://community.cratedb.com/t/query-using-max-on-column-using-calculated-partition-key-on-that-column-not-performing-as-expected/1918/14 "2024-12-18T11:03:55Z")

</div>

Hi,

> [@ness](#):
>
> should the indexing of this TIMESTAMP WITHOUT TIME ZONE field be able to return the max immediately?

This is [not currently the case](https://github.com/crate/crate/issues/16782#issuecomment-2419229380), what you could maybe do here is try a different number of shards to see if a different level of parallelism may help.  
Also you will probably see that if you narrow the search space further within the partition identifying the max is much faster, so with some additional logic, if you know the general area where the last\_response may be, with some trial and error/binary search kind of strategy you may get results quicker.