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