> I don't think that index can be used for your original query. It could
> only be used if "channel" is unique in "valueseries" and you'd written
> the query as:
Thanks! That explanation I can understand, now I know how to avoid this
in future.
> I guess "channel" must not be the primary key to "valueseries" and
> that's why you use an IN().
Correct. We create a new valueseries in some circumstances, so multiple
valueseries can point to the same channel.
On 2022-04-27 10:22, David Rowley wrote:
On Wed, 27 Apr 2022 at 19:54, Emil Iggland <emil.iggland@xxxxxxxxxxx> wrote:
> You've got the wrong column order (for this query anyway) in that
> index. It'd work a lot better if dataview were the first column;
I might be misunderstanding you, but I assume that you are suggesting an
index on (dataview, valuetimestamp).
We have that index, it is the primary key. For some reason it isn't
being selected.
I don't think that index can be used for your original query. It could
only be used if "channel" is unique in "valueseries" and you'd written
the query as:
select * from datavalue
where dataview = (select id from valueseries where channel = 752433)
ORDER BY VALUETIMESTAMP DESC
FETCH FIRST ROW only;
that would allow a backwards index scan using the (dataview,
valuetimestamp) index. Because you're using the IN clause to possibly
look for multiple "dataview" values matching the given "channel", the
index range scan does not have a single point to start at. What
you've done with the LATERAL query allows the index to be scanned once
for each "valueseries" row with a "channel" value matching your WHERE
clause.
I guess "channel" must not be the primary key to "valueseries" and
that's why you use an IN().
The above query would return an error if multiple rows were returned
by the subquery.
David
