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 < msdem...@ari.uni-heidelberg.de> 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

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

On Fri, Oct 28, 2022 at 2:41 AM Peter J. Holzer wrote: > > > Up until 11.17, the source of the docker images was tag "postgres:11" > > (from https://hub.docker.com/_/postgres), for 11.17 the tag became > > "postgres:11-bullseye" but as far as i could tell it was just a > > difference of tagging po

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

On 2022-10-27 15:07:06 +0300, Kristjan Mustkivi wrote: > On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > > We use dockerized postgres. > > > > So that means you aren't just replacing PostgreSQL, but your complete OS > > (except

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

On 10/27/22 07:07, Kristjan Mustkivi wrote: On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: We use dockerized postgres. So that means you aren't just replacing PostgreSQL, but your complete OS (except the kernel). What is the sour

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

On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > We use dockerized postgres. > > So that means you aren't just replacing PostgreSQL, but your complete OS > (except the kernel). What is the source of your docker images? Do they >

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

On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > We use dockerized postgres. > > So that means you aren't just replacing PostgreSQL, but your complete OS > (except the kernel). What is the source of your docker images? Do they >

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

On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > We use dockerized postgres. So that means you aren't just replacing PostgreSQL, but your complete OS (except the kernel). What is the source of your docker images? Do they all use the same base OS distribution? Are the locale definitions the

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

Hi Allan, We use dockerized postgres. So the upgrade is just replacing the old minor version pg docker image with new minor version docker image and that's it. Of course, I am checking the release notes to see if there is anything to pay attention to particularly. I do apologize, but I do not und

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

On Thu, Oct 27, 2022 at 10:26 AM Allan Kamau wrote: > > > On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi > wrote: > >> Dear community, >> >> Right after upgrading our postgres servers from 11.15 to 11.17 we >> started to encounter problems with data. Namely, when the query hit >> the index,

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

On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi wrote: > Dear community, > > Right after upgrading our postgres servers from 11.15 to 11.17 we > started to encounter problems with data. Namely, when the query hit > the index, it returned a single row; when the query hit a relation > directly,

Re: index row size 2720 exceeds btree version 4

On 2022-08-10 10:27:46 +0530, Daulat wrote: > Error: > > " index row size 2720 exceeds btree version 4 maximum 2704 for index > ""uk_gvoa_gi_ad"" 54000" > uk_gvoa_gi_ad > > Index: > > UNIQUE INDEX pk_gvoa_id ON test.groupedvuln_asset USING btree > (groupedvuln_orphanasset_id)" > > UNIQUE INDEX

Re: index row size 2720 exceeds btree version 4

Am 10.08.22 06:57 schrieb(en) Daulat: We are facing an error while uploading data in a table that has two B-tree indexes. As per the Postgres documentation there is a limitation of b-tree index size but I need to know if there is any alternative to overcome this issue. [snip] UNIQUE INDEX uk_gv

Re: Index only scans for expressional indices when querying for the expression

Danny Shemesh writes: > Do you think there'd be room to accept a contribution for such > functionality with a disabled-by-default pg setting, > or are you skeptical it would ever be worth the trade-off ? If you can show me a matching algorithm with non-exponential runtime, I'd be interested.

Re: Index only scans for expressional indices when querying for the expression

A-ha, interesting ! I think we have some specific use cases where it'd be worth the overhead, I'd need to measure it, though; Do you think there'd be room to accept a contribution for such functionality with a disabled-by-default pg setting, or are you skeptical it would ever be worth the trade-o

Re: Index only scans for expressional indices when querying for the expression

Danny Shemesh writes: > That is of course correct, but what I mean is that, I think that if one > would explicitly query f(x), and never for x directly, it would've been > theoretically possible to say that the index is covering for every f(x), > wouldn't it ? Theoretically, yeah, but we don't su

Re: Index only scans for expressional indices when querying for the expression

Hey David - thanks for the prompt response ! That is of course correct, but what I mean is that, I think that if one would explicitly query f(x), and never for x directly, it would've been theoretically possible to say that the index is covering for every f(x), wouldn't it ? Or in other words, if

Re: Index only scans for expressional indices when querying for the expression

On Thursday, August 4, 2022, Danny Shemesh wrote: > > I believe the expressional index in itself could've been considered as > covering, when querying for the expression explicitly. > This belief is wrong. When storing f(x) there is no way to recover the value of x. David J.

Re: Index creation

Yes, you are right. The presented index usage data is caused by recursive queries, which check the integrity of hierarchical structures from the bottom up. Your explanation has clarified what is going on. Thank you. My experiments with indexes are caused by the appearance of significant variance (1

Re: Index creation

On Mon, Jun 20, 2022 at 1:17 AM Дмитрий Иванов wrote: > Your statement seems obvious to me. But what I see doesn't seem like a > conscious choice. It turns out that it is better to have a lighter > general-purpose index than to strive to create a target covering index for > a certain kind of oper

Re: Index creation

Your statement seems obvious to me. But what I see doesn't seem like a conscious choice. It turns out that it is better to have a lighter general-purpose index than to strive to create a target covering index for a certain kind of operation. DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inheri

Re: Index creation

On Sun, Jun 19, 2022 at 12:06 PM Дмитрий Иванов wrote: > Good afternoon. > I have a query parser question. If there are two kinds of queries using an > indexed field. In this case, one view is limited to this field, the second > one uses a number of fields included in the index by the include dir

Re: index unique

On 2021-06-11 14:37:57 +0200, Marc Millas wrote: > anyway, my original post was about the fact that we must create a PK based on > some kind of work around the limited btree length behaviour thus creating an > index that will never be of any use, just for technical reasons. Actually, that index wi

Re: index unique

Hi Alban, I plainly agree on the uniqueness thing. and on the fact that a PK with only a geometry column can be considered somehow "ill suited". That said, the PK we finally use contains, as said, 3 columns: --an id (integer column) --a topology describer (the text column) --and the geometry colum

Re: index unique

> On 8 Jun 2021, at 22:50, Thomas Kellerer wrote: > > Marc Millas schrieb am 03.06.2021 um 22:51: >> on a table we need a primary key and to get a unique combinaison, we need 3 >> columns of that table: >> 1 of type integer, >> 1 of type text, >> 1 of type geometry >> > > How do you define t

Re: index unique

Thanks Thomas, but, as stated after the first post, the need was for a PK as asked by postgres (ie. for tech needs, not for functionnal needs) up to now, looks like we must create a PK (and so, the associated index) just to answer logical replication needs.(and qgis which also needs a PK) that ind

Re: index unique

Marc Millas schrieb am 03.06.2021 um 22:51: on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table: 1 of type integer, 1 of type text, 1 of type geometry creating the PK constraint doesn work: (even with our current small data set) ERROR:  index row si

Re: index unique

Marc Millas writes: > I know, its clearly stated in postgres doc about btree," The only > limitation is that an index entry cannot exceed approximately one-third of > a page (after TOAST compression, if applicable)." Yup. > is there any plan to adress this ? No. The cost/benefit ratio seems co

Re: index unique

On Tuesday, June 8, 2021, Marc Millas wrote: > > but as this looks quite hard coded, it means that for long utf8 things > the data length is not so long before hitting the limit. > > is there any plan to adress this ? > None that I’ve seen, and I don’t expect to see one either. Mainly because

Re: index unique

Cristal clear ! and it have to be the case as my test was done with some not so random data. but this mean that we cannot put a bunch of datatypes in a PK, as soon as it may be longer than 2701. I know, its clearly stated in postgres doc about btree," The only limitation is that an index entry can

Re: index unique

On 2021-06-08 18:30:16 +0200, Marc Millas wrote: > the only pb observed is the size of the object accepted. if the geom is a bit > "big" then the index errors.about btree size of index object. > but if I create a table test_l with a text column blabla as a PK, and insert a > 100 000 character long

Re: index unique

quite funny to see how a tech question seems to end into an english grammar thing :-) quote > You make this sound like an either-or proposition, While he is talking about *a* primary key, it should be *the* primary key. There can be only one (that's why it is the primary key). There can be sever

Re: index unique

On 2021-06-07 10:20:22 -0700, David G. Johnston wrote: > On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer wrote: > On 2021-06-03 22:51:55 +0200, Marc Millas wrote: > > postgres 12 with postgis. > > on a table we need a primary key and to get a unique combinaison, we > need > > 3 co

Re: index unique

On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer wrote: > On 2021-06-03 22:51:55 +0200, Marc Millas wrote: > > postgres 12 with postgis. > > on a table we need a primary key and to get a unique combinaison, we > need 3 > > columns of that table: > > 1 of type integer, > > 1 of type text, > > 1 of

Re: index unique

On 2021-06-03 22:51:55 +0200, Marc Millas wrote: > postgres 12 with postgis. > on a table we need a primary key and to get a unique combinaison, we need 3 > columns of that table: > 1 of type integer, > 1 of type text, > 1 of type geometry > > creating the PK constraint doesn work: I find that if

Re: index unique

Hi Paul, obviously the bounding box is not a perfect choice. we also think of one of the "centers" point, but quite similar non uniqueness. so, if no "tech" solution, we continue to work with the business to try to find an appropriate PK thanks Marc MILLAS Senior Architect +33607850334 www.moka

Re: index unique

Primary key is going to be a BTREE index. I'm surprised you require the geometry in order to achieve uniqueness? You can't put the geometry into a BTREE because it's too large. You could add a column and stick the MD5 hash of the geometry there, and use that as the last piece of uniqueness? If t

Re: index only scan taking longer to run

On Thu, 29 Apr 2021 at 21:02, Ayub M wrote: > > In the below execution plan, the index scan on five_lima (table has 900m > records) is where it's spending most of its time. I want to bring down the > runtime to a few seconds, how do I optimize it? Tried forcing seq scan and > ran vacuum/analyze

Re: index only scan taking longer to run

Without knowledge of the structure of your data, or even real table/field names to be able to read the query with some sense of what the tables might be like, offering advice is rather difficult. I didn't notice any nodes with huge estimation problems at least. I will suggest that DISTINCT is ofte

Re: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04

Thanks, that was quick. Didn't realise the distribution's locale tables would have been a source of differences too -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04

On Tue, Feb 2, 2021 at 11:20 AM unilynx wrote: > > I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/ > - I've got a database created under Ubuntu 18.04, and recently updated to > Ubuntu 20.04. These are all docker builds This is a known problem when upgrading Ubuntu (an

Re: Index tuple deduplication limitations in pg13

On Tue, Aug 18, 2020 at 1:31 PM Matthias van de Meent wrote: > Would this extra ordering not effectively be an extra tiebreaker in > the ordering, applied before the TID? I do not know the full > implications of that, but I believe that would not result in the > limitations that you are mentioning

Re: Index tuple deduplication limitations in pg13

On Tue, 18 Aug 2020 at 22:00, Peter Geoghegan wrote: > > On Tue, Aug 18, 2020 at 11:52 AM Matthias van de Meent > wrote: > > Given that the above could work, the current btree tuple ordering is > > not optimized for opclass-equal but datum image-distinct values: > > ordering of opclass-equal valu

Re: Index tuple deduplication limitations in pg13

On Tue, 18 Aug 2020 at 18:44, Peter Geoghegan wrote: > > On Mon, Aug 17, 2020 at 11:44 PM Matthias van de Meent > wrote: > > But, if the ordering of operator-class equal tuples is already > > system-defined, could the physical ordering of index tuples in a btree > > (with deduplication enabled fo

Re: Index tuple deduplication limitations in pg13

On Tue, Aug 18, 2020 at 11:52 AM Matthias van de Meent wrote: > Given that the above could work, the current btree tuple ordering is > not optimized for opclass-equal but datum image-distinct values: > ordering of opclass-equal values is currently determined only by tid, > with as an example curre

Re: Index tuple deduplication limitations in pg13

On Tue, Aug 18, 2020 at 11:52 AM Matthias van de Meent wrote: > Deduplication does not need to destroy semantic differences? 'equal' > can (in my book) mean: > - 'opclass-equal', that is the opclass returns true for an equality check > - 'binary equal' or 'datum-equal' (? maybe incorrect term), th

Re: Index tuple deduplication limitations in pg13

On Tue, Aug 18, 2020 at 9:44 AM Peter Geoghegan wrote: > If we wanted to fix this for numeric, we'd have to invent a new > numeric datatype (called numeric2, say). That probably isn't as hard > as it sounds, since it could be part of the same B-Tree operator > family as numeric. It could also be i

Re: Index tuple deduplication limitations in pg13

On Mon, Aug 17, 2020 at 11:44 PM Matthias van de Meent wrote: > But, if the ordering of operator-class equal tuples is already > system-defined, could the physical ordering of index tuples in a btree > (with deduplication enabled for "unsafe" opclasses) be updated from > [index_columns, tid] to [i

Re: index numbers in pg_restore list

Thanks Tom and Adrian, The clarity is helpful - We'll run up a solution to specifically choose the elements. cheers Ben On Sat, 15 Aug 2020 at 00:45, Tom Lane wrote: > Ben Madin writes: > > " Is the index number ( the archive ID) assigned at the time of creation > > of the archive and not

Re: index numbers in pg_restore list

Ben Madin writes: > " Is the index number ( the archive ID) assigned at the time of creation > of the archive and not otherwise related to the item in the > original database?" Yes, it's just assigned internally during pg_dump. I think the numbers would hold still as long as you make no DDL cha

Re: index numbers in pg_restore list

On 8/14/20 9:16 AM, Ben Madin wrote: Dear all, I was hoping for some quick guidance on the structure of the pg_restore list. We have a database which we are restoring each day for a data warehouse. For efficiency, we are not restoring a number of tables and functions, just the tables, index

Re: Index no longer being used, destroying and recreating it restores use.

On Tue, Jun 16, 2020 at 11:49:15AM +0200, Koen De Groote wrote: > Alright, I've done that, and that seems to be a very good result: https:// > explain.depesz.com/s/xIph > > The method I ended up using: > > create or replace function still_needs_backup(shouldbebackedup bool, > backupperformed bool

Re: Index no longer being used, destroying and recreating it restores use.

Alright, I've done that, and that seems to be a very good result: https://explain.depesz.com/s/xIph The method I ended up using: create or replace function still_needs_backup(shouldbebackedup bool, backupperformed bool) returns BOOLEAN as $$ select $1 AND NOT $2; $$ language sql immutable; An

Re: Index no longer being used, destroying and recreating it restores use.

On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote wrote: > Right. In that case, the function I ended up with is this: > > create or replace function still_needs_backup(bool, bool) > returns BOOLEAN as $$ > BEGIN > PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2; > IF FO

Re: Index no longer being used, destroying and recreating it restores use.

On Tue, Jun 9, 2020 at 02:23:51PM +0200, Koen De Groote wrote: > Right. In that case, the function I ended up with is this: > > create or replace function still_needs_backup(bool, bool) > returns BOOLEAN as $$ > BEGIN                 >     PERFORM 1 from item where shouldbebackedup=$1 and backupp

Re: Index no longer being used, destroying and recreating it restores use.

Right. In that case, the function I ended up with is this: create or replace function still_needs_backup(bool, bool) returns BOOLEAN as $$ BEGIN PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE;

Re: Index no longer being used, destroying and recreating it restores use.

On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote wrote: > So, this query: > > select * from item where shouldbebackedup=true and > itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by > filepath asc, id asc limit 100 offset 10400; > > Was made into a function: > > create or repl

Re: Index no longer being used, destroying and recreating it restores use.

So, this query: select * from item where shouldbebackedup=true and itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by filepath asc, id asc limit 100 offset 10400; Was made into a function: create or replace function NeedsBackup(text, int, int default 100) returns setof ite

Re: Index no longer being used, destroying and recreating it restores use.

I'll attempt this next week. On Fri, Jun 5, 2020, 21:11 Michael Lewis wrote: > Those row estimates are pretty far off. > > Standard indexes and partial indexes don't get custom statistics created > on them, but functional indexes do. I wonder if a small function > needs_backup( shouldbebackedup,

Re: Index no longer being used, destroying and recreating it restores use.

Those row estimates are pretty far off. Standard indexes and partial indexes don't get custom statistics created on them, but functional indexes do. I wonder if a small function needs_backup( shouldbebackedup, backupperformed ) and an index created on that function would nicely alleviate the pain.

Re: Index no longer being used, destroying and recreating it restores use.

The requested result: https://explain.depesz.com/s/G7mU Also, the data from the statistic itself: => SELECT stxname, stxkeys, stxdependencies -> FROM pg_statistic_ext -> WHERE stxname = 's1'; stxname | stxkeys | stxdependencies -+-+- s1 | 29 35 |

Re: Index no longer being used, destroying and recreating it restores use.

On 6/5/20 7:05 AM, Koen De Groote wrote: I've collected all relevant info(I think so at least) and put it here: The table in question is used to keep filepath data, of files on a harddrive. The query in question is used to retrieve items which should be backed up, but have not yet been. The

  1   2   >