Обсуждение: Faster distinct query?

On Sep 22, 2021, at 12:20 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Sep 22, 2021 at 1:05 PM Israel Brewster <ijbrewster@alaska.edu> wrote:

To work around the issue, I created a materialized view that I can update periodically, and of course I can query said view in no time flat. However, I’m concerned that as the dataset grows, the time it takes to refresh the view will also grow (correct me if I am wrong there).

I'd probably turn that index into a foreign key that just ensures that every (station,channel) that appears in the data table also appears on the lookup table.  Grouping and array-ifying the lookup table would be trivial.  Either modify the application code or add a trigger to populate the lookup table as needed.

The parentheses around channel in "array_agg(distinct(channel))" are unnecessary - you are invoking composite-type syntax, which is ignored in the single column case unless you write the optional ROW keyword, i.e., distinct ROW(channel)

David J.

On Sep 22, 2021, at 12:49 PM, Ryan Booz <ryan@timescale.com> wrote:

[Timescale Dev Advocate here]
I realize this might not be the most accepted answer (could be interpreted as trying to "sell" something), but feels like an opportunity to talk about DISTINCT queries and opportunities. Because you have that index, Timescale 2.3 added a "Skip Scan" query planner node that works on regular BTree indexes (it doesn't have to be time-series/TimescaleDB Hypertable data at all). In this case, your distinct query would likely run in a few milliseconds based on the counts you mention (170 stations, 3 channels per station), and then the outer aggregation would do the GROUP BY. So, you **could** add the TimescaleDB extension to your database (or a copy of) and give it a try. You don't actually need to use any TimescaleDB features otherwise.

• A writeup of why this kind of DISTINCT query is slow in PostgreSQL (for now) and what we did to overcome it: https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/
• Plans for a similar feature in PostgreSQL proper that we'd totally support but hasn't made forward progress yet: https://commitfest.postgresql.org/19/1741/

Anyway, it might be worth a shot. HTH

Ryan B

On Wed, Sep 22, 2021 at 4:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@entrata.com> wrote:

In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables,

I believe we consider it acceptable to link to an explain viewer, which is what the OP did.  Reading explain output in email has its own challenges, and I'd rather have the website than a text attachment.

How does the below work? It should do a very simple index scan only, then aggregate the relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels

FROM(

SELECT station,channel FROM data GROUP BY station,channel

) AS sub

group by station;

Yeah, am pondering this too, though seems like the queries should be identical so the plan/execution should be the same either way.

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices

There is no where clause so I'm doubtful there is much to be gained going down this path.  The Index-Only scan seems like an optimal way to obtain this data and the existing query already does that.  The aggregation path might vary though it seems like that shouldn't be the case here.

David J.

On Sep 22, 2021, at 1:50 PM, Ryan Booz <ryan@timescale.com> wrote:

Cool. I'd be interested to see the explain on it if you ever try it again. On that cardinality, I'd expect it to be really fast, so I'm interested to see if the (SkipScan) nodes were actually used.

On Wed, Sep 22, 2021 at 5:35 PM Israel Brewster <ijbrewster@alaska.edu> wrote:

On Sep 22, 2021, at 12:49 PM, Ryan Booz <ryan@timescale.com> wrote:

[Timescale Dev Advocate here]
I realize this might not be the most accepted answer (could be interpreted as trying to "sell" something), but feels like an opportunity to talk about DISTINCT queries and opportunities. Because you have that index, Timescale 2.3 added a "Skip Scan" query planner node that works on regular BTree indexes (it doesn't have to be time-series/TimescaleDB Hypertable data at all). In this case, your distinct query would likely run in a few milliseconds based on the counts you mention (170 stations, 3 channels per station), and then the outer aggregation would do the GROUP BY. So, you **could** add the TimescaleDB extension to your database (or a copy of) and give it a try. You don't actually need to use any TimescaleDB features otherwise.

I had actually already done that, as I was considering, in spite of past negative experiences with timescaledb, experimenting with it on this DB to see if it worked any better with this data. Out of curiosity, I tried removing the timescaledb extension, whereupon the query in question took roughly twice as long. So you are right that installing timescaledb speeds things up, even when not using any timescaledb specific functions. So that was a good call. Thanks!

---

Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320

Work: 907-474-5172
cell:  907-328-9145

• A writeup of why this kind of DISTINCT query is slow in PostgreSQL (for now) and what we did to overcome it: https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/
• Plans for a similar feature in PostgreSQL proper that we'd totally support but hasn't made forward progress yet: https://commitfest.postgresql.org/19/1741/

Anyway, it might be worth a shot. HTH

Ryan B

On Wed, Sep 22, 2021 at 4:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@entrata.com> wrote:

In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables,

I believe we consider it acceptable to link to an explain viewer, which is what the OP did.  Reading explain output in email has its own challenges, and I'd rather have the website than a text attachment.

How does the below work? It should do a very simple index scan only, then aggregate the relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels

FROM(

SELECT station,channel FROM data GROUP BY station,channel

) AS sub

group by station;

Yeah, am pondering this too, though seems like the queries should be identical so the plan/execution should be the same either way.

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices

There is no where clause so I'm doubtful there is much to be gained going down this path.  The Index-Only scan seems like an optimal way to obtain this data and the existing query already does that.  The aggregation path might vary though it seems like that shouldn't be the case here.

David J.

On Sep 22, 2021, at 2:05 PM, Ryan Booz <ryan@timescale.com> wrote:

Ah. I didn't realize that. If SkipScan was chosen, you'd actually see it as one of the execution nodes. I also realize I was making a few assumptions about your data, are channels shared among stations, or are all channels unique (like an ID) per station? That would impact the index and approach.

Something like:

station | channel
----------|-----------

1            1

1            2

2            3

2            4

or:

station | channel
----------|-----------

1            1

1            2

2            1

2            2

On Wed, Sep 22, 2021 at 5:53 PM Israel Brewster <ijbrewster@alaska.edu> wrote:

On Sep 22, 2021, at 1:50 PM, Ryan Booz <ryan@timescale.com> wrote:

Cool. I'd be interested to see the explain on it if you ever try it again. On that cardinality, I'd expect it to be really fast, so I'm interested to see if the (SkipScan) nodes were actually used.

With timescaledb extension installed, the explain is what I posted in the original message (https://explain.depesz.com/s/mtxB#html). Without timescaledb installed, the explain looks the same, except it takes twice as long to run.

Unless I missed something in your message, i.e. some sort of tweak to the query to get it to use the timescaledb features?

---

Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320

Work: 907-474-5172
cell:  907-328-9145

On Wed, Sep 22, 2021 at 5:35 PM Israel Brewster <ijbrewster@alaska.edu> wrote:

On Sep 22, 2021, at 12:49 PM, Ryan Booz <ryan@timescale.com> wrote:

[Timescale Dev Advocate here]
I realize this might not be the most accepted answer (could be interpreted as trying to "sell" something), but feels like an opportunity to talk about DISTINCT queries and opportunities. Because you have that index, Timescale 2.3 added a "Skip Scan" query planner node that works on regular BTree indexes (it doesn't have to be time-series/TimescaleDB Hypertable data at all). In this case, your distinct query would likely run in a few milliseconds based on the counts you mention (170 stations, 3 channels per station), and then the outer aggregation would do the GROUP BY. So, you **could** add the TimescaleDB extension to your database (or a copy of) and give it a try. You don't actually need to use any TimescaleDB features otherwise.

I had actually already done that, as I was considering, in spite of past negative experiences with timescaledb, experimenting with it on this DB to see if it worked any better with this data. Out of curiosity, I tried removing the timescaledb extension, whereupon the query in question took roughly twice as long. So you are right that installing timescaledb speeds things up, even when not using any timescaledb specific functions. So that was a good call. Thanks!

---

Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320

Work: 907-474-5172
cell:  907-328-9145

• A writeup of why this kind of DISTINCT query is slow in PostgreSQL (for now) and what we did to overcome it: https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/
• Plans for a similar feature in PostgreSQL proper that we'd totally support but hasn't made forward progress yet: https://commitfest.postgresql.org/19/1741/

Anyway, it might be worth a shot. HTH

Ryan B

On Wed, Sep 22, 2021 at 4:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@entrata.com> wrote:

In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables,

I believe we consider it acceptable to link to an explain viewer, which is what the OP did.  Reading explain output in email has its own challenges, and I'd rather have the website than a text attachment.

How does the below work? It should do a very simple index scan only, then aggregate the relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels

FROM(

SELECT station,channel FROM data GROUP BY station,channel

) AS sub

group by station;

Yeah, am pondering this too, though seems like the queries should be identical so the plan/execution should be the same either way.

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices

There is no where clause so I'm doubtful there is much to be gained going down this path.  The Index-Only scan seems like an optimal way to obtain this data and the existing query already does that.  The aggregation path might vary though it seems like that shouldn't be the case here.

David J.

On Sep 22, 2021, at 5:10 PM, David Rowley <dgrowleyml@gmail.com> wrote:

On Thu, 23 Sept 2021 at 08:27, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis <mlewis@entrata.com> wrote:

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices

There is no where clause so I'm doubtful there is much to be gained going down this path.  The Index-Only scan seems like an optimal way to obtain this data and the existing query already does that.  The aggregation path might vary though it seems like that shouldn't be the case here.

ndistinct extended statistics would be used to estimate the number of
groups in the GROUP BY clause for the version of the query that
performs GROUP BY station, channel.   We've not seen the EXPLAIN
ANALYZE for that query, so don't know if there's any use for extended
statistics there.  However, if the planner was to think there were
more groups than there actually are, then it would be less inclined to
do parallelise the GROUP BY.  I think writing the query in such a way
that allows it to be parallelised is likely going to result in some
quite good performance improvements. i.e:

select station, array_agg(channel) as channels
FROM(
  SELECT station,channel FROM data GROUP BY station,channel
) AS sub
group by station;

which is just the same as Michael's version but without DISTINCT.
Also, Tom's point about lots of heap fetches is going to count for
quite a bit too, especially so if I/O plays a large part in the total
query time.

David

On Sep 23, 2021, at 4:34 AM, Ryan Booz <ryan@timescale.com> wrote:

Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions.

This is what depesz is referring to: https://wiki.postgresql.org/wiki/Loose_indexscan

On Thu, Sep 23, 2021 at 3:04 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:

On Wed, Sep 22, 2021 at 12:05:22PM -0800, Israel Brewster wrote:
> I was wondering if there was any way to improve the performance of this query:
>
> SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY station;
>
> The explain execution plan can be found here:
> https://explain.depesz.com/s/mtxB#html <https://explain.depesz.com/s/mtxB#html>
>
> and it looks pretty straight forward. It does an index_only scan, followed by an aggregate, to produce a result that is a list of stations along with a list of channels associated with each (there can be anywhere from 1 to 3 channels associated with each station). This query takes around 5 minutes to run.
>
> To work around the issue, I created a materialized view that I can update periodically, and of course I can query said view in no time flat. However, I’m concerned that as the dataset grows, the time it takes to refresh the view will also grow (correct me if I am wrong there).
>
> This is running PostgreSQL 13, and the index referenced is a two-column index on data(station, channel)

It looks that there is ~ 170 stations, and ~ 800 million rows int he
table.

can you tell us how many rows has this:

select distinct station, channel from data;

If this is not huge, then you can make the query run much faster using
skip scan - recursive cte.

Best regards,

depesz

On Sep 23, 2021, at 8:33 AM, Rob Sargent <robjsargent@gmail.com> wrote:

On 9/23/21 10:16 AM, Israel Brewster wrote:

On Sep 23, 2021, at 4:34 AM, Ryan Booz <ryan@timescale.com> wrote:

Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions.

This is what depesz is referring to: https://wiki.postgresql.org/wiki/Loose_indexscan

Thanks for the pointer. Will definitely have to spend some time wrapping my brain around that one - I’ve done some CTE’s before, but not recursive that I can recall. Should be fun!

If it helps matters any, my structure is currently the following:

table “stations” listing station details (name, latitude, longitude, etc) with a smallint primary key “id"

table “data” with many (many!) data columns (mostly doubles), a station column that is a smallint referencing the stations table, and a channel column which is a varchar containing the *name* of the channel the data came in on.

I will readily accept that this may not be the best structure for the DB. For example, perhaps the channel column should be normalized out as has been mentioned a couple of times as an option. This would make sense, and would certainly simplify this portion of the project.

If I do go with a lookup table updated by a trigger, what would be the best option for the query the trigger runs - an upset (ON CONFLICT DO NOTHING)? Or a query followed by an insert if needed? The normal case would be that the entry already exists (millions of hits vs only the occasional insert needed).

I would look into pre-loading the lookup table (and pre-emptive maintenance).  Add the foreign key, but not the trigger.

On Sep 23, 2021, at 10:36 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

On Wed, 22 Sept 2021 at 21:05, Israel Brewster <ijbrewster@alaska.edu> wrote:

I was wondering if there was any way to improve the performance of this query:

SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY station;

If you have tables of possible stations and channels (and if not, why not?), then an EXISTS query, something like

SELECT stations.name, ARRAY_AGG(channels.name)
  FROM stations, channels
  WHERE EXISTS
   (SELECT FROM data WHERE data.channels=channels.name AND data.station=stations.name)
GROUP BY stations.name

will usually be much faster, because it can stop scanning after the first match in the index.

Geoff

On Sep 23, 2021, at 8:55 PM, Michael Lewis <mlewis@entrata.com> wrote:

It’s only when I add in the AND data.channels=channels.channel that the query time blows up to 5+ minutes. I personally don’t understand why there would be such a large difference between the two queries - something wrong with my indexes?

Did you do the manual vacuum as suggested by Tom?

Maybe at least check pg_stat_user_tables for the last vacuum or autovacuum timestamp, and how many records have changed since then.

The system is deciding on an index scan because that should be very fast, but if it needs to look at the actual table table to determine if the process executing that query should still be allowed to see that tuple (row version) then the index only scan slows down a lot I believe. The huge number of heap fetches that Tom pointed out mean that the system doesn't know that ALL processes should be able to see those tuples. As someone else suggested lowering the max freeze age, you might just do a manual "vacuum freeze" as well to allow future auto vacuum processes to quickly skip over those pages as well.

I've heard of the loose indexscan before mentioned on this thread, but I'm not seeing how to implement that for multiple columns. Anyone have an example or pseudo code perhaps?