Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 11/6/25 16:30, Bala M wrote: * * *1) Show how you have set up the publishers and subscribers.* We have configured logical replication between PostgreSQL 11 (publisher) and PostgreSQL 16 (subscriber). Publisher: PostgreSQL 11 with wal_level=logical, max_replication_slots=20, and publicatio

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

*1) Show how you have set up the publishers and subscribers.* We have configured logical replication between PostgreSQL 11 (publisher) and PostgreSQL 16 (subscriber). Publisher: PostgreSQL 11 with wal_level=logical, max_replication_slots=20, and publication created for all tables (FOR ALL TABLES)

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

Hi Adrian, Thank you for your response. Please find the requested details below: *PostgreSQL Version:* Source: PostgreSQL 11.15 Target: PostgreSQL 16.9 *Operating System:* Source: RHEL 7.9 Target: RHEL 9.6 *Network Distance:* Both servers are in the same data center, connected through a hi

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 11/6/25 09:04, Bala M wrote: Hi  Adrian, Thank you for your response. Please find the requested details below: Since you have already started is that not already to late for this? Yes We are currently in the *testing phase* and validating with the above parameters. However, the re

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 11/4/25 22:27, Bala M wrote: Thank you all for your suggestions, Thanks for your quick response and for sharing the details. After reviewing the options, the logical replication approach seems to be the most feasible one with minimal downtime. However, we currently have 7 streaming replica

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

Thank you all for your suggestions, Thanks for your quick response and for sharing the details. After reviewing the options, the logical replication approach seems to be the most feasible one with minimal downtime. However, we currently have 7 streaming replication setups running from production,

Re: Index (primary key) corrupt?

On 10/28/25 03:25, Wim Rouquart wrote: Internal I used the output from DBeaver, guess it acted up. Here’s the output from psql: 1) As side note, find a different client to use then DBeaver. I have seen many Postgres questions on Stack Overflow where the answer was, use something other then

Re: Index (primary key) corrupt?

Could you run the SELECT on pg_index after the REINDEX to see if there is any difference?

RE: Index (primary key) corrupt?

Internal I used the output from DBeaver, guess it acted up. Here’s the output from psql: select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; -[ RECORD 1 ]---+ indexrelid | 2006873 indrelid| 1998823 indnatts| 1 indnkeyatts

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 2025-10-25 10:39:35 -0400, Ron Johnson wrote: > On Sat, Oct 25, 2025 at 10:21 AM Adrian Klaver > wrote: > > On 10/24/25 21:50, David Rowley wrote: > > Because people promote the .0 as not yet production-ready, it means > > that fewer people bother testing with beta and RC versions.

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Sat, Oct 25, 2025 at 10:21 AM Adrian Klaver wrote: > On 10/24/25 21:50, David Rowley wrote: > > On Sat, 25 Oct 2025 at 17:36, Adrian Klaver > wrote: > >> I am not following, from your previous post: > >> > >> "Beta versions are meant for test instances. It'd be > >> good if people encouraged

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 21:50, David Rowley wrote: On Sat, 25 Oct 2025 at 17:36, Adrian Klaver wrote: I am not following, from your previous post: "Beta versions are meant for test instances. It'd be good if people encouraged their use more often rather than pushing people to defer til GA" That seems to

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 2025-10-25 17:50:59 +1300, David Rowley wrote: > On Sat, 25 Oct 2025 at 17:36, Adrian Klaver wrote: > > I am not following, from your previous post: > > > > "Beta versions are meant for test instances. It'd be > > good if people encouraged their use more often rather than pushing > > people to

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 18:53, Tom Lane wrote: David Rowley writes: On Sat, 25 Oct 2025 at 13:40, Adrian Klaver wrote: 1) From previous posts to this list folks have mentioned their organizations prohibit touching anything less then a GA or maybe a late RC. That comes from on high and I doubt the folks i

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Sat, 25 Oct 2025 at 17:36, Adrian Klaver wrote: > I am not following, from your previous post: > > "Beta versions are meant for test instances. It'd be > good if people encouraged their use more often rather than pushing > people to defer til GA" > > That seems to be the opposite of what you sa

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 18:06, David Rowley wrote: On Sat, 25 Oct 2025 at 13:40, Adrian Klaver wrote: 2) The attitude comes from lessons learned in the School of Hard Knocks. Until someone or someones can guarantee a new GA release will not eat your data or spring security leaks then the prudent thing

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Sat, 25 Oct 2025 at 14:53, Tom Lane wrote: > > David Rowley writes: > > On Sat, 25 Oct 2025 at 13:40, Adrian Klaver > > wrote: > >> 1) From previous posts to this list folks have mentioned their > >> organizations prohibit touching anything less then a GA or maybe a late > >> RC. That comes

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

David Rowley writes: > On Sat, 25 Oct 2025 at 13:40, Adrian Klaver wrote: >> 1) From previous posts to this list folks have mentioned their >> organizations prohibit touching anything less then a GA or maybe a late >> RC. That comes from on high and I doubt the folks issuing the orders are >> on

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Sat, 25 Oct 2025 at 13:40, Adrian Klaver wrote: > > On 10/24/25 15:37, David Rowley wrote: > > On Sat, 25 Oct 2025 at 04:51, Adrian Klaver > > wrote: > >> > >> On 10/24/25 08:00, Ron Johnson wrote: > > I'd say it's exactly that attitude that causes people to think .0 > > should be avoided. Be

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 15:37, David Rowley wrote: On Sat, 25 Oct 2025 at 04:51, Adrian Klaver wrote: On 10/24/25 08:00, Ron Johnson wrote: On Fri, Oct 24, 2025 at 10:54 AM Adrian Klaver mailto:[email protected]>> wrote: "Never trust a .0 release with important data" is just as true in 2025 as

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Friday, October 24, 2025, David Rowley wrote: > On Sat, 25 Oct 2025 at 04:51, Adrian Klaver > wrote: > > > > On 10/24/25 08:00, Ron Johnson wrote: > > > On Fri, Oct 24, 2025 at 10:54 AM Adrian Klaver > > > mailto:[email protected]>> wrote: > > > > > "Never trust a .0 release with impo

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Sat, 25 Oct 2025 at 04:51, Adrian Klaver wrote: > > On 10/24/25 08:00, Ron Johnson wrote: > > On Fri, Oct 24, 2025 at 10:54 AM Adrian Klaver > > mailto:[email protected]>> wrote: > > > "Never trust a .0 release with important data" is just as true in 2025 > > as it was in 1985. > > > >

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, 23 Oct 2025 at 17:21, Greg Sabino Mullane wrote pg_dump is the most reliable, and the slowest. Keep in mind that only the > actual data needs to move over (not the indexes, which get rebuilt after > the data is loaded). You could also mix-n-match pg_logical and pg_dump if > you have a few

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 08:00, Ron Johnson wrote: On Fri, Oct 24, 2025 at 10:54 AM Adrian Klaver mailto:[email protected]>> wrote: "Never trust a .0 release with important data" is just as true in 2025 as it was in 1985. That's a chicken and egg problem, though, isn't it? There is nothing stop

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Fri, Oct 24, 2025 at 10:54 AM Adrian Klaver wrote: > On 10/24/25 05:53, Greg Sabino Mullane wrote: > > On Thu, Oct 23, 2025 at 11:49 AM Adrian Klaver > > mailto:[email protected]>> wrote: > > > > I am not sure version 18 would a good choice at this time, it has > > just been re

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/24/25 05:53, Greg Sabino Mullane wrote: On Thu, Oct 23, 2025 at 11:49 AM Adrian Klaver mailto:[email protected]>> wrote: I am not sure version 18 would a good choice at this time, it has just been released and has no bug fix releases against it yet. Given the other

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Fri, Oct 24, 2025 at 8:59 AM Greg Sabino Mullane wrote: > On Thu, Oct 23, 2025 at 10:51 AM Bala M wrote: > >> Any advice, recommendations, or shared experiences from others who have >> performed similar migrations would be greatly appreciated. > > > Some related advice: put some system in pla

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, Oct 23, 2025 at 10:51 AM Bala M wrote: > Any advice, recommendations, or shared experiences from others who have > performed similar migrations would be greatly appreciated. Some related advice: put some system in place to make sure you are always running the latest revision in whatever

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, Oct 23, 2025 at 11:49 AM Adrian Klaver wrote: > I am not sure version 18 would a good choice at this time, it has just > been released and has no bug fix releases against it yet. Given the > other complications in your upgrade, OS upgrade and multi-version jump, I > don't think you want t

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, Oct 23, 2025 at 4:24 PM Scot Kreienkamp < [email protected]> wrote: > > I always assumed streaming would “just work” as long as it’s the same > major PG version and Linux-to-Linux regardless of OS/glibc versionIt > never occurred to me that there could be an OS influencing f

RE: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

//instagram.com/lazboy> | youtube.com/lazboy<http://youtube.com/lazboy> [cid:lazboy_2024_inc_navy_4a4d68ec-613a-4141-a2aa-d73a2ae749f6.png] From: Devrim Gündüz Sent: Thursday, October 23, 2025 2:17 PM To: [email protected]; Bala M Subject: Re: Index corruption issue aft

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Fri, 24 Oct 2025 at 07:17, Devrim Gündüz wrote: > This happens because of the glibc version differrence between RHEL X and RHEL > Y. At this point you either have to rebuild all indexes (sorry!) or redo the > upgrade via logical replication (if it works for your app's behaviour) There was a

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

Hi, This happens because of the glibc version differrence between RHEL X and RHEL Y. At this point you either have to rebuild all indexes (sorry!) or redo the upgrade via logical replication (if it works for your app's behaviour) Devrim On 22 October 2025 12:03:06 EEST, Bala M wrote: >Hi Tea

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, Oct 23, 2025 at 11:21 AM Greg Sabino Mullane wrote: > >>- >> >>*Acceptable downtime:* ~1 day >>- >> >>*Logical replication:* Not feasible due to the number of schemas, >>tables, and overall data volume >> >> I'm not sure why this is not feasible. Can you expand on this

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/23/25 07:51, Bala M wrote: Hi All, Thank you for the valuable responses regarding the *index corruption issue* we observed during our test migration of a *PostgreSQL 11* environment from *RHEL 7 to RHEL 9* using *streaming replication*. Based on the replies, I understand that. S

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

> > >- > >*Acceptable downtime:* ~1 day >- > >*Logical replication:* Not feasible due to the number of schemas, >tables, and overall data volume > > I'm not sure why this is not feasible. Can you expand on this? * For a *15 TB database* with roughly *1 day downtime*, what would

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/22/25 11:18, Ron Johnson wrote: On Wed, Oct 22, 2025 at 1:35 PM Greg Sabino Mullane > wrote: On Wed, Oct 22, 2025 at 6:53 AM Bala M mailto:[email protected]>> wrote: * PostgreSQL Version: 11.15 Ouch! Not only is the major v

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Wed, Oct 22, 2025 at 1:35 PM Greg Sabino Mullane wrote: > On Wed, Oct 22, 2025 at 6:53 AM Bala M wrote: > >> >>- >> >>PostgreSQL Version: 11.15 >> >> > Ouch! Not only is the major version end of life, but that's not even the > latest revision of 11. > Do pre-built PG11 binaries even

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Wed, Oct 22, 2025 at 6:53 AM Bala M wrote: > >- > >PostgreSQL Version: 11.15 > > Ouch! Not only is the major version end of life, but that's not even the latest revision of 11. At this point, you should use logical replication to migrate from your v11 to a shiny new Postgres v18 on you

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On 10/22/25 02:03, Bala M wrote: Hi Team, We are facing an issue related to index corruption after migrating our PostgreSQL 11 setup from *RHEL 7* to *RHEL 9* using *streaming replication* (base backup method). Postgres 11 is two years past EOL per: https://www.postgresql.org/support/versio

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

Hi Krishna, This might be due to the upgrade in glibc between RHEL7 and RHEL8, which includes updated collations that change sort orders and thus invalidate existing indexes, forcing them to be rebuilt (on a RHEL>7 system) if they contain any values which sort differently under the new collation t

RE: Index (primary key) corrupt?

Internal Hi, Apologies for the late response, had other fish to fry... In response to your questions: > What is full(15.x) version of Postgres are you using? 15.14 >Is it the community version or a fork or SaaS? Standard release indeed, running on RHAT8 > What do you get for queries below?:

RE: Index (primary key) corrupt?

Internal Yes indeed, i just restore the database to before the rebuild. > So the problem goes away once you’ve reindexed yet you claim it’s consistent? > What are you doing to get the problem to recur after you’ve done reindex to > make it work? > > David I was assuming the OP has a dump o

Re: Index (primary key) corrupt?

On Sat, Sep 20, 2025 at 5:07 AM Wim Rouquart wrote: > Name |Value | > ---+---+ > indexrelid |2006873| > indrelid |1998823| > indnatts |1 | > indnkeyatts|1 | ... indclass |{} | > Hold on, that makes n

Re: Index (primary key) corrupt?

On 10/10/25 05:28, Wim Rouquart wrote: Internal Hi, Apologies for the late response, had other fish to fry... In response to your questions: What is full(15.x) version of Postgres are you using? 15.14 Is it the community version or a fork or SaaS? Standard release indeed, running on RH

Re: Index rebuilding strategy

On Wed, Sep 24, 2025 at 4:51 PM Alban Hertroys wrote: > > > On 24 Sep 2025, at 22:42, Siraj G wrote: > > > > Hello Experts! > > > > What are the top pointers we should consider for index rebuild? Check > its size, bloat estimate, heavy Updates/Deletes? > > > > Please highlight the best practice

Re: Index rebuilding strategy

On Thu, 2025-09-25 at 02:12 +0530, Siraj G wrote: > What are the top pointers we should consider for index rebuild? Check its > size,  bloat estimate, heavy Updates/Deletes? https://www.cybertec-postgresql.com/en/should-i-rebuild-my-postgresql-index/ Yours, Laurenz Albe

Re: Index rebuilding strategy

> On 24 Sep 2025, at 22:42, Siraj G wrote: > > Hello Experts! > > What are the top pointers we should consider for index rebuild? Check its > size, bloat estimate, heavy Updates/Deletes? > > Please highlight the best practices. I think just any pointers of corruption, really. OS updates wi

Re: Index (primary key) corrupt?

> On Sep 20, 2025, at 9:58 AM, Adrian Klaver wrote: > On 9/20/25 09:26, Rob Sargent wrote: >>> So the problem goes away once you’ve reindexed yet you claim it’s >>> consistent? What are you doing to get the problem to recur after you’ve >>> done reindex to make it work? >>> David >> I was as

Re: Index (primary key) corrupt?

On 9/20/25 09:26, Rob Sargent wrote: So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem to recur after you’ve done reindex to make it work? David I was assuming the OP has a dump of the affected condition and is restoring (

Re: Index (primary key) corrupt?

> So the problem goes away once you’ve reindexed yet you claim it’s consistent? > What are you doing to get the problem to recur after you’ve done reindex to > make it work? > > David I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No?

Re: Index (primary key) corrupt?

On Friday, September 19, 2025, Wim Rouquart wrote: > > >> Does this problem keep happening, or has it only happened once? > > > > It is consistent on this database/index, haven’t noticed it anywhere else > yet luckily. Seems to be a one-off. > > So the problem goes away once you’ve reindexed yet

Re: Index (primary key) corrupt?

On 9/19/25 02:29, Wim Rouquart wrote: Internal Does this problem keep happening, or has it only happened once? It is consistent on this database/index, haven’t noticed it anywhere else yet luckily. Seems to be a one-off. The above is not clear to me. One-off as for this database/index on

RE: Index (primary key) corrupt?

Internal >> Internal >> >> >> Hello, >> >>When doing a pg_dump of one of our databases one of the tables primary >> keys doesn’t get exported. Pg_dump just skips this index, without any >> warning whatsoever (verbose mode was used to doublecheck). > What is the complete table definition? CREATE

RE: Index (primary key) corrupt?

Internal Hello, When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck). >> >>> What is the complete table definition? >> >> CREAT

RE: Index (primary key) corrupt?

Internal Hello, When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck). When doing a REINDEX the issue is fixed. As this seems to me to be some form

Re: Index (primary key) corrupt?

On 9/19/25 06:23, Wim Rouquart wrote: What is the result for the query?: select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; Name |Value | ---+---+ indexrelid |2006873| indrelid |1998823| indnatts |1

Re: Index (primary key) corrupt?

On 9/19/25 02:40, Wim Rouquart wrote: Internal Internal Hello, When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck). What is the complete table

Re: Index (primary key) corrupt?

On 9/18/25 05:25, Wim Rouquart wrote: Internal Hello, When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck). What is the complete table definitio

Re: Index (primary key) corrupt?

On Thu, Sep 18, 2025 at 10:58 AM Wim Rouquart wrote: > Hello, > > > When doing a pg_dump of one of our databases one of the tables primary > keys doesn’t get exported. Pg_dump just skips this index, without any > warning whatsoever (verbose mode was used to doublecheck). > > > > When doing a REIN

Re: Index not used in certain nested views but not in others

Markus Demleitner writes: > On Tue, May 13, 2025 at 10:12:46AM -0400, Tom Lane wrote: >> Oh, well, that's your problem. The source tables' column types >> need to match. Otherwise the UNIONs don't get flattened and you >> don't get indexscans. > A... *source* tables. Sure, once I'm out of

Re: Index not used in certain nested views but not in others

Dear Tom, On Tue, May 13, 2025 at 10:12:46AM -0400, Tom Lane wrote: > Markus Demleitner writes: > > SELECT > > CAST(ssa_dstype AS text) AS dataproduct_type, > > CAST(NULL AS text) AS dataproduct_subtype, > > CAST(2 AS smallint) AS calib_level, > > ... > > Oh, well, that's your p

Re: Index not used in certain nested views but not in others

Markus Demleitner writes: > You see, when creating the "big", 30-tables view, I do cast all > columns to common types in the view statement that actually make up > the view. The original SQL fragments look like this: > SELECT > CAST(ssa_dstype AS text) AS dataproduct_type, > CAST(NULL

Re: Index not used in certain nested views but not in others

Dear Tom, Dear Laurenz, Thanks for your pointers -- that was already helpful. On Thu, May 01, 2025 at 06:58:45AM +0200, Laurenz Albe wrote: > On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote: > > It's hard to be sure when you've shown us no table definitions and > > only fragments of the view de

Re: Index not used in certain nested views but not in others

"Peter J. Holzer" writes: > On 2025-04-30 17:16:47 +0200, Markus Demleitner wrote: >> dfbsspec.raw_spectra is about 23 Megarows, >> -> Parallel Seq Scan on raw_spectra (cost=0.00..2626995.66 rows=5803266 >> width=756) (actual time=0.137..6841.379 rows=4642657 loops=5) > It estimates that it ha

Re: Index not used in certain nested views but not in others

On 2025-04-30 17:16:47 +0200, Markus Demleitner wrote: > There is another view made up of about 20 tables, looking somewhat > like this: [...] > The first view, ivoa.obs_radio, is just a few hundred records, > dfbsspec.raw_spectra is about 23 Megarows, [...] >

Re: Index not used in certain nested views but not in others

On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote: > Markus Demleitner writes: > > So, when the SELECT statement on dfbsspec.ssa stands along in the view > > definition, Postgres does the right thing; when the exact same query > > stands in a UNION ALL with other tables, Postgres doesn't use the >

Re: Index not used in certain nested views but not in others

Markus Demleitner writes: > So, when the SELECT statement on dfbsspec.ssa stands along in the view > definition, Postgres does the right thing; when the exact same query > stands in a UNION ALL with other tables, Postgres doesn't use the > index. Hu? It's hard to be sure when you've shown us no

Re: Index not used in certain nested views but not in others

On Wed, Apr 30, 2025 at 3:51 PM Markus Demleitner < [email protected]> wrote: > Dear List, > > I know how tedious mails with a subject of the type "I don't understand > what the planner does" are, but on this one I'm really stumped. > Regrettably, the situation is also a bit complex.

Re: Index usage with differing string types

Henning Garus writes: > However when the String is cast to text the index isn't used: > explain select * from test where id = 'foo'::text; That's because "text" is considered a preferred type, so it wins the contest over whether '=' means texteq or bpchareq: # explain select * from test where id

Re: Index usage with differing string types

On 2/4/25 08:23, Henning Garus wrote: Hi, I stumbled upon this behaviour when digging into the performance of some merge statements generated by hibernate. Looking at different String types (varchar, text and bpchar) in some cases an index is used when the index type differs from the type in

Re: Index Partition Size Double of its Table Partition?

On Thu, Oct 31, 2024 at 11:13 AM Rob Sargent wrote: > Whats the fill factor? > No fill factor is specified, so I'm assuming it's the default 90% for indexes. FYI we did a REINDEX for the index in question tonight. Since the index was for last month, there are no more writes to it so we didn't u

Re: Index Partition Size Double of its Table Partition?

On Oct 31, 2024, at 10:02 AM, Don Seiler wrote:On Wed, Oct 30, 2024 at 4:59 PM David Mullineux wrote:Are you able to cluster the table ? The idea is that rows ordered in the same way  as the index might reduce it's size ? I'm not sure on this. There are other indexes on these t

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 4:59 PM David Mullineux wrote: > Are you able to cluster the table ? The idea is that rows ordered in the > same way as the index might reduce it's size ? > I'm not sure on this. There are other indexes on these table partitions as well. Another bit of useful info that I

Re: Index Partition Size Double of its Table Partition?

Are you able to cluster the table ? The idea is that rows ordered in the same way as the index might reduce it's size ? On Wed, 30 Oct 2024, 16:29 Don Seiler, wrote: > On Wed, Oct 30, 2024 at 11:23 AM Peter Geoghegan wrote: > >> >> If a substantial amount of the index was written by CREATE IND

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 11:23 AM Peter Geoghegan wrote: > > If a substantial amount of the index was written by CREATE INDEX (and > not by retail inserts) then my theory is unlikely to be correct. It > could just be that you managed to absorb most inserts in one > partition, but not in the other.

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 12:08 PM Don Seiler wrote: > Why would last month's index be so much smaller? Because the split heuristics worked as designed there. That's the theory, at least. > Both indexes were created using CONCURRENTLY, as each was created during its > month when we started testin

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 10:45 AM Peter Geoghegan wrote: > > It sounds like you have no updates and deletes. Right? So the only > thing that could be different is the way that the pages are being > split (aside from variations in the width of index tuples, which seems > highly unlikely to be the o

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 11:39 AM Don Seiler wrote: > Thanks Peter, I'll look into that shortly. It sounds like you have no updates and deletes. Right? So the only thing that could be different is the way that the pages are being split (aside from variations in the width of index tuples, which see

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 10:35 AM Peter Geoghegan wrote: > On Wed, Oct 30, 2024 at 11:24 AM Don Seiler wrote: > > One thing worth mentioning is that the table is 4 columns, the index is > on two of them and includes the other two. I can't think of an explanation > for the index being so much larg

Re: Index Partition Size Double of its Table Partition?

On Wed, Oct 30, 2024 at 11:24 AM Don Seiler wrote: > One thing worth mentioning is that the table is 4 columns, the index is on > two of them and includes the other two. I can't think of an explanation for > the index being so much larger than its table, especially compared to last > month's in

Re: Index based search have issues with 11.20

Thanks for the details We will further check on the references given On Tue, Oct 17, 2023 at 10:20 PM Adrian Klaver wrote: > On 10/17/23 11:24, Hafeez Rahim wrote: > > > > Hi > > > > This is to check regarding an issue we came across on postgreSQL > > community version 11 with minor patch 11.20

Re: Index based search have issues with 11.20

Thanks for the providing the references we will go through the wiki link and identify the glibc version updates during the OS patching. On Tue, Oct 17, 2023 at 10:18 PM Tom Lane wrote: > Hafeez Rahim writes: > > Issue : > > > One of the table query not woks when string values are filtered wit

Re: Index based search have issues with 11.20

On 10/17/23 11:24, Hafeez Rahim wrote: Hi This is to check regarding an issue we came across on postgreSQL community version 11 with minor patch 11.20 along with few other changes change details mention on end part of this mail The issue is notice after the minor patching from postgreSQL 11.

Re: Index based search have issues with 11.20

Hafeez Rahim writes: > Issue : > One of the table query not woks when string values are filtered with = > clause > where as the query works with like clause using '%' flag or using > trim(column) > below are query examples describes the problem You've not really provided enough detail, but I'm

Re: Index scan is not pushed down to union all subquery

On Thu, Oct 5, 2023 at 4:25 PM Lauri Kajan wrote: > In my dreams the plan would be something like this: > Nested Loop > -> Index Scan using dealers_pkey on dealers > Index Cond: (id = ANY ('{54,12,456,315,468}'::integer[])) > -> Append > -> Bitmap Heap Scan on bikes >

Re: Index scan is not pushed down to union all subquery

On Thu, Oct 5, 2023 at 12:30 PM Marian Wendt wrote: > > With an INNER JOIN, both tables must be fully checked/matched (check using EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here. > Sorry, didn't consider the WITH part. Please share the detailed query plan for more info. Now, it r

Re: Index scan is not pushed down to union all subquery

On Thu, Oct 5, 2023 at 11:35 AM Marian Wendt wrote: > With an INNER JOIN, both tables must be fully checked/matched (check using > EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here. > Sorry, didn't consider the WITH part. Please share the detailed query > plan for more info. > > The

Re: Index scan is not pushed down to union all subquery

Hi, Is it intended that indexes are not pushed down to union all subqueries if even a single select contains a where clause? Is this just not implemented, is it impossible to implement or am I doing something wrong? The following query does a SeqScan for "bikes" and "cars" tables even though Ind

Re: Index scan is not pushed down to union all subquery

Hi, Is it intended that indexes are not pushed down to union all subqueries if even a single select contains a where clause? Is this just not implemented, is it impossible to implement or am I doing something wrong? The following query does a SeqScan for "bikes" and "cars" tables even though Ind

Re: Index scan is not pushed down to union all subquery

Hi, Is it intended that indexes are not pushed down to union all subqueries if even a single select contains a where clause? Is this just not implemented, is it impossible to implement or am I doing something wrong? The following query does a SeqScan for "bikes" and "cars" tables even though Ind

Re: Index scan for PK constraint validation

On Wednesday, February 8, 2023, Philippe VIEGAS wrote: > Hi all, > > I was wondering why the index statistics usage were not reflecting the > index usage for primary key constraint validation ? > > When we create a table with a primary key, PostgreSQL creates a Btree > index for the validation of

Re: Index-only scan not working when IN clause has 2 or more values

=?UTF-8?B?QW5uYSBCLg==?= writes: > create index "ix-transaction-client-trans_dttm-division" > on transaction (client_id, > trans_dttm desc, > division_code); > > EXPLAIN (ANALYZE, BUFFERS) > select * > from transaction > where client_id = 123456 > an

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

Hey Laurenz, Tom - thanks again ! > that it is cheaper to use the index that supports the ORDER BY Thing is, that both queries use the exact same index (idx_hashes), but one uses it w/ the filter and one does not. > This doesn't match up terribly well with the table definition you showed before Y

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

Danny Shemesh writes: > -> Index Only Scan using > idx_hashes on refs (cost=0.56..722735.47 rows=33715 width=16) (actual > time=1727.208..1727.208 rows=1 loops=1) > Index Cond: (tid = > '13371337-1337-1337-1337

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

On Wed, 2022-11-23 at 15:38 +0200, Danny Shemesh wrote: > ->  Limit  (cost=0.56..24.17 rows=1 width=16) (actual time=2657.167..2657.167 > rows=1 loops=39) >       Buffers: shared hit=323452 read=887661 >       I/O Timings: read=2369932.536 >       ->  Index Only Scan using idx_hashes on refs refs_

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

Hey Laurenz, thanks for the prompt response ! What I meant is this - the plan consists of either an index scan that uses all passed columns in the index condition, or an index scan that uses only one column as an index condition, with an additional filter step. The below are explain (analyze, bu

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

On Wed, 2022-11-23 at 10:49 +0200, Danny Shemesh wrote: > I'm trying to understand when the planner decides to use an index condition > vs an index filter I am not sure what you mean by "index filter". If you could send the result of EXPLAIN (ANALYZE, BUFFERS) for the queries, that would be most

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 10/28/22 16:24, Peter J. Holzer wrote: On 2022-10-28 17:23:02 +0300, Kristjan Mustkivi wrote: By the way, index rebuild while completing successfully did not fix the indexes - the data in the tables was still missing even after the successful rebuild command. This is surprising. As I unders

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 2022-10-28 17:23:02 +0300, Kristjan Mustkivi wrote: > By the way, index rebuild while completing successfully did not fix > the indexes - the data in the tables was still missing even after the > successful rebuild command. This is surprising. As I understand it, REINDEX scans the complete tabl

  1   2   >