Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint

Ron Johnson writes: > On Fri, May 10, 2024 at 11:28 PM WU Yan <4wu...@gmail.com> wrote: >> Simple query that uses the multicolumn index. >> postgres=# explain (analyze, buffers) select * from t where row(a, b) > >> row(123450, 123450) and a = 0 order by a, b; > Out of curiosity, why "where

Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint

On Fri, May 10, 2024 at 11:28 PM WU Yan <4wu...@gmail.com> wrote: > Hi everyone, first time here. Please kindly let me know if this is not the > right place to ask. > > I notice a simple query can read a lot of buffer blocks in a meaningless > way, when > 1. there is an index scan on a

Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint

Hi everyone, first time here. Please kindly let me know if this is not the right place to ask. I notice a simple query can read a lot of buffer blocks in a meaningless way, when 1. there is an index scan on a multicolumn index 2. there is row constructor comparison in the Index Cond 3. there is

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Sat, 11 May 2024 at 13:33, Tom Lane wrote: > I do kind of wonder why it's producing both a hashagg and a Unique > step --- seems like it should do one or the other. It still needs to make the duplicate groups from parallel workers unique. David

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou wrote: > Indeed that's an awful estimate, the table has more than 1M of unique > values in that column. Looking into pg_stat_user_tables, I can't see the > partitions having been vacuum'd or analyzed at all. I think they should > have been

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

Dimitrios Apostolou writes: > On Fri, 10 May 2024, Tom Lane wrote: >> I'd say the blame lies with that (probably-default) estimate of >> just 200 distinct rows. That means the planner expects to have >> to read about 5% (10/200) of the tables to get the result, and >> that's making fast-start

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Fri, 10 May 2024, Tom Lane wrote: Dimitrios Apostolou writes: Further digging into this simple query, if I force the non-parallel plan by setting max_parallel_workers_per_gather TO 0, I see that the query planner comes up with a cost much higher: Limit (cost=363.84..1134528847.47

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

Dimitrios Apostolou writes: > Further digging into this simple query, if I force the non-parallel plan > by setting max_parallel_workers_per_gather TO 0, I see that the query > planner comes up with a cost much higher: > Limit (cost=363.84..1134528847.47 rows=10 width=4) > -> Unique

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Fri, 10 May 2024, Dimitrios Apostolou wrote: On Fri, 10 May 2024, Dimitrios Apostolou wrote: Update: even the simplest SELECT DISTINCT query shows similar behaviour: Further digging into this simple query, if I force the non-parallel plan by setting max_parallel_workers_per_gather TO

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Fri, 10 May 2024, Dimitrios Apostolou wrote: I noticed that the following query is very very slow (too long to wait for it to finish): SELECT DISTINCT workitem_n FROM test_runs_raw ORDER BY workitem_n DESC LIMIT 10; Update: even the simplest SELECT DISTINCT query shows similar behaviour:

SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

Hello list, INTRO I have a huge (multi-billion rows) table partitioned into 1000 partitions. Around half of the partitions are full and the rest are empty, created in advance ready to receive future incoming data. Postgres is 16.2. Here are the relevant parts of the schema: \d test_runs_raw

Re: Postgresql active-active nodes in cluster

Thanks all for your inputs. * Currently I'm working on active-passive(Primary and standby scenario). * I'm preparing one study report on active-active node implementation which talks about active-active nodes background, theoretical explanation, implementation part which includes some

Re: Unexpected data when subscribing to logical replication slot

Thank you all for your input. We have solved the problem by - 1. Configuring wal2json to include xids . 2. Updating our enrichment queries to return the xmin .

Scenarios that Replication Slot will be Marked as Active = false

Hi Experts, I would like to write a monitoring script that will check logical replication running on our setup. For that I would like to know when to alert when I see that replication slot is marked as active= false. I will probably need to know when it is a temporary state that I need to

Re: Question on roles and privileges

On Fri, May 10, 2024 at 11:31 AM Lok P wrote: > For the initial installation the extensions may need superuser privileges. > >> >> Thank you. Yes, I got it. For the initial installation for the extensions ,it will need super user privilege. But once that is done for the day to day use , does

Re: Question on roles and privileges

For the initial installation the extensions may need superuser privileges. On Fri, May 10, 2024 at 10:04 AM yudhi s wrote: > Hello All, > We want to make sure to keep minimal privileges for the users based on > their roles and responsibility. We have one user group who will be working > on