From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | basti <mailinglist(at)unix-solution(dot)de> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Query take a long time and use no index |
Date: | 2023-07-18 11:34:39 |
Message-ID: | CAApHDvqJ=Ozw==92X6dC0oK5c-vhRb_XcM7TZX6j_+rP191dcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 17 Jul 2023 at 21:13, basti <mailinglist(at)unix-solution(dot)de> wrote:
> volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day',
> TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data
> WHERE channel_id = 5 AND timestamp >= 0;
Alternatively, you could express this as:
SELECT COUNT(*) FROM (SELECT DISTINCT DATE_TRUNC('day', TIMESTAMP
'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data WHERE
channel_id = 5 AND timestamp >= 0) a;
If there was an index on (channel_id, (DATE_TRUNC('day', TIMESTAMP
'epoch' + timestamp * INTERVAL '1 millisecond'))); then the distinct
could efficiently perform a Group Aggregate. Otherwise, it could at
least hash aggregate and the distinct could be done in parallel
(assuming you're using at least PostgreSQL 15).
The yet-to-be-released PostgreSQL 16 will allow more efficient
execution of DISTINCT and ORDER BY aggregates by allowing indexed to
provide pre-sorted input. In the meantime, the query above will
probably help you.
David
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-07-18 14:44:03 | Re: Upgrade Failure |
Previous Message | David Rowley | 2023-07-18 11:28:02 | Re: Query take a long time and use no index |