Re: Odd Row Estimates in Query Plan (rows=75)

On 08/15/2018 01:03 PM, Don Seiler wrote:
> Here's the query, obfuscated manually by me:
> 
> SELECT
>          'Foo' as system_function,
> stores.name <http://stores.name> as store,
>          lt.owner,
>          lt.minute_of_day,
>          lt.records
>          FROM
>          foo.stores
>          LEFT OUTER JOIN
>              (SELECT
>                  lts.store_pkey,
>                  lts.owner,
>                  date_trunc('minute', lts.date_gifted) as minute_of_day,
>                  count(*) as records
>              FROM foo.gifts lts
>              WHERE
>                  lts.date_added  > '2017-07-14 11:13:05'
>              AND lts.date_added  < '2017-08-13 14:14:21'
>              AND lts.date_gifted >= '2017-08-13 11:13:05'
>              AND lts.date_gifted <  '2017-08-13 14:14:21'
>              GROUP BY 1,2,3
>              ORDER BY 1
>              ) lt ON lt.store_pkey = stores.pkey
>          WHERE lt.records IS NOT NULL;
> 
> The foo.gifts table is pretty much the core table of our database. It's 
> big and very active. There is an index on date_added but not yet on 
> date_gifted.
> 
> I'm working to re-write the query while the dev sees if we even need 
> this query anymore.
> 

I agree the issue seems to be in the index/filter of the dates. That 
leads me to another question:

Why in:

WHERE
    lts.date_added  > '2017-07-14 11:13:05'
AND
    lts.date_added  < '2017-08-13 14:14:21'
AND
    lts.date_gifted >= '2017-08-13 11:13:05'
AND
    lts.date_gifted <  '2017-08-13 14:14:21'

is

    lts.date_added  > '2017-07-14 11:13:05'

and
    
    lts.date_gifted >= '2017-08-13 11:13:05'
?

In other words one '>' and the other '>=' ?








-- 
Adrian Klaver
adrian.klaver@aklaver.com