Re: Query take a long time and use no index - Mailing list pgsql-general

From David Rowley
Subject Re: Query take a long time and use no index
Date
Msg-id CAApHDvqJ=Ozw==92X6dC0oK5c-vhRb_XcM7TZX6j_+rP191dcA@mail.gmail.com
Whole thread Raw
In response to Query take a long time and use no index  (basti <mailinglist@unix-solution.de>)
List pgsql-general
On Mon, 17 Jul 2023 at 21:13, basti <mailinglist@unix-solution.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



pgsql-general by date:

Previous
From: David Rowley
Date:
Subject: Re: Query take a long time and use no index
Next
From: Ron
Date:
Subject: Re: Upgrade Failure