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@xxxxxxxxxxx
