Сould not find pathkey item to sort

TimescaleDB and PostgreSQL
, ,
1

Hi, all!

Postgres version: PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
TimscaleDB version: 2.12.1
Hypertable is compressed.

If the query joins compressed hypertable with regular postgres table and period is larger then few days then exception is threw

SELECT date_trunc('day', a.date_col) AS col_date,
	a.col2,
	b.col3,
	COUNT(DISTINCT a.id_account),
	SUM(a.num_col1 - a.num_col2)
FROM compressed_hypert AS a
	INNER JOIN postgres_table AS b ON b.id = a.id
WHERE a.col_date >= TIMESTAMP '2023-10-01' AND a.col_date < TIMESTAMP '2023-11-15'
GROUP BY date_trunc('day', a.date_col), a.col2, b.col3
;

SQL Error [XX000]: ERROR: could not find pathkey item to sort

If I reduce period to few days then the query gives the result successfully.

About this I found issue on github but the decision I did not find.

Is there a workaround to do the query like above working?

Thanks in advance.

2

Hi Lux, not sure if I can answer it but any reason to not use time_bucket instead of date_trunc? Have you tried it?

3

Unfortunately, it is not matter: time_bucket gives the same result.

4

Can it be related to the timescaledb.compress_orderby? It’s very important for performance too. Not sure if you have it configured or not.

5

Yes, I configured both parameters:

timescaledb.compress_orderby = 'id',
timescaledb.compress_segmentby = 'another_col1, another_col2'

And one more thing: the table has been partitioned by month since 2017, the data in the partitions since 2019 is constantly updated.

6

The only practical fix I see is this part:

For me, GROUP BY SMALLINT type columns didn’t work.
Casting to ::INTEGER fixed it.

have you checked the types compatibility?

It seems a very old chunk, probably If you’re able to put it into a minimal reproducible example it will simply work. If you try let us know, at least we can understand if something was left behind and it’s a migration pitfall rather than a feature error :thinking:

7

I recreated the hypertable with integer type for fields instead of smallint and the query worked.
But this behavior is not obvious.

Thank you @jonatasdp

1 Like
8

Hey @LuxCore, even if this conversation is resolved, feel free to create a GitHub ticket. Probably more users will use other int types as you did.

1 Like
9

My issue was querying a compressed chunk using an “order by” on a field that is not part of the key.