https://questdb.io logo
Title
j

John M.

03/08/2023, 6:16 PM
I'm trying to see how many records we have in each partition (our table is parted by day) -- what's the most efficient way to do that? is there any way to use a partition in a group by? sample by 1d works, but seems to be surprisingly not performant. this query took 48 seconds for ~month of financial market data. seems like if you know the timestamp field size a simple count(time) should be able to determine the # records per partition simply by dividing file size by the bytes/record, so I'm wondering if there's some group by partition or whatnot.
select time, count(time) from tablename sample by 1d
a

Alex Pelagenko

03/08/2023, 7:11 PM
I could run faster, if you run count day by day it will be optimised
select count() from trades
where timestamp in '2023-01-01'
but sample is not optimised for this case
j

John M.

03/08/2023, 7:11 PM
that would only work for a single day though right? so getting a count by date, across multiple dates, wouldnt work performantly?
a

Alex Pelagenko

03/08/2023, 7:12 PM
you can do union all 😉
j

John M.

03/08/2023, 7:12 PM
interesting, will try to figure out how to do that
a

Alex Pelagenko

03/08/2023, 7:13 PM
select count() from trades
where timestamp in '2023-01-01'
union all
select count() from trades
where timestamp in '2023-01-02'
...
j

John M.

03/08/2023, 7:13 PM
prob need something to programmatically spit out dates to construct the query but yeah that would work - thank you