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(dot)klaver(at)aklaver(dot)com