> 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@metrima.com> 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
