Re: Sort is generating rows

On 05/31/2018 02:09 AM, Nicolas Seinlet wrote:
> Hi,
> 
> I have a query with a strange query plan.
> 
> This query is roughly searching for sales, and convert them with a 
> currency rate. As currency rate changes from time to time, table 
> contains the currency, the company, the rate, the start date of 
> availability of this rate and the end date of availability.

My guess is to get a complete answer you are going to need to provide:

1) The complete query.

2) The complete EXPLAIN ANALYZE.

More comments inline below.

> 
> The join is done using :
>      left join currency_rate cr on (cr.currency_id = pp.currency_id and
>            cr.company_id = s.company_id and
>            cr.date_start <= coalesce(s.date_order, now()) and
>           (cr.date_end is null or cr.date_end > coalesce(s.date_order, 
> now())))
> 
> The tricky part is the date range on the currency rate, which is not an 
> equality.

> 
> My question is then , is there a better way to join a table to another 
> using a date range, knowing that there's no overlap between date ranges?

Use date ranges?:

https://www.postgresql.org/docs/10/static/functions-range.html


> Should we generate a virtual table with rates for all dates, and joining 
> using an equality?
> 
> For now, the more currency rates, the slowest the query. There's not 
> that much currency rates (1k in this case), as you can only have one 
> rate per day per currency.
> 
> Have a nice day,
> 
> Nicolas.


-- 
Adrian Klaver
adrian.klaver@aklaver.com