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

y > > For a more detailed description of that problem, see > https://www.cybertec-postgresql.com/en/union-all-data-types-performance/ I've puzzled over this for a while, and while I'm sure the type mixing is what kills the index usage here, I've been unable to actually pinpoin

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

rows=561 width=0) (actual >> time=2.478..2.479 rows=0 loops=1) >> -> Seq Scan on main (cost=0.00..52.61 rows=561 width=48) (actual >> time=0.011..0.317 rows=561 loops=1) >> -> Index Scan using raw_spectra_pub_did on raw_spectra >> (cost=

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

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) [...] > > My problem is: I can't seem to figure out why Postgres chooses to ignore > the pubdid index on raw_spect

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

tgres doesn't use the > > index. Hu? > > It's hard to be sure when you've shown us no table definitions and > only fragments of the view definitions. But I suspect what is > happening here is that the view's UNIONs are causing a data type > coercion of raw

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 w

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.

Index not used in certain nested views but not in others

.raw_spectra LEFT JOIN dfbsspec.platemeta ON platemeta.plateid = raw_spectra.plate) q raw_spectra finally is a physical table that has an index: "raw_spectra_pub_did" btree (pub_did) The first view, ivoa.obs_radio, is just a few hundred records, dfbsspec.raw_spectra is about

Re: Custom index access method for primary keys

Fabian Lindfors writes: > Hi! I’m working on an index access method that I intend to be > generic and to replace btree for my specific use case. I noticed > though that it doesn’t seem to be possible to specify an access > method when creating a new table and using PRIMARY KEY. There

Custom index access method for primary keys

Hi! I’m working on an index access method that I intend to be generic and to replace btree for my specific use case. I noticed though that it doesn’t seem to be possible to specify an access method when creating a new table and using PRIMARY KEY. Same applies to altering a table to add a

Re: Postgres Query Plan using wrong index

resulting query plan which uses just the index on col_b: postgres=# explain analyze select min(col_b) from test_table where col_a > 4996; Result (cost=62.13..62.14 rows=1 width=4) (actual time=536.648..536.649 rows=1 loop

Re: Postgres Query Plan using wrong index

oorly performing index? I already have an index on (col_a, col_b) that performs well. When I remove the separate index on (col_b), it correctly uses the (col_a, col_b) index and the query runs efficiently. But when both indexes are present, it chooses the slower (col_b) index instead. 2) Why woul

Re: Postgres Query Plan using wrong index

4996 were right at the start of an ordered scan of the idx_col_b_a index, it would have been a good plan. There might be just as many people getting good plans as there are bad and adding pessimism here might just make one set of people happy and the others sad. I don't have a clear idea, but

Re: Postgres Query Plan using wrong index

n the columns mentioned in the ON > clause. What we'd need to store to do better in your example query is > positional information of where certain values are within indexes > according to an ordered scan of the index. I don't quite know how we'd > represent that exactly, bu

Re: Postgres Query Plan using wrong index

y is positional information of where certain values are within indexes according to an ordered scan of the index. I don't quite know how we'd represent that exactly, but if we knew that a row matching col_a > 4996 wasn't until somewhere near the end of idx_col_a_btree index, then we'd likely not want to use that index for this query. David

Re: Postgres Query Plan using wrong index

Manikandan Swaminathan writes: > 1) Why is the query currently picking the poorly performing index? Because the planner thinks that one will be cheaper, as you can see by comparing the cost estimates in EXPLAIN. It's wrong, but this is a hard problem to estimate well. Especially

Postgres Query Plan using wrong index

Hello, I'm running on the docker postgres:17.0 image and trying to test out the behavior of adding a new index to a table. Specifically, I wanted to verify that my new index is actually used by looking at the output of "EXPLAIN ANALYZE". However, I found that my index is often not

Re: Postgres Query Plan using wrong index

Manikandan Swaminathan writes: > 4. When running the following query, I would expect the index "idx_col_b_a" > to be used: select min(col_b) from test_table where col_a > 4996. > I have a range-based filter on col_a, and am aggregating the result with > min(col_b). Bo

Re: BTREE index: field ordering

? You currently have "tipo" in second place in your candidate index. Thanks, Kevin From: Moreno Andreo Sent: Friday, March 28, 2025 5:38 AM To: PostgreSQL mailing lists Subject: BTREE index: field ordering Hi, Postgres 16.4 (planning to go on 17.4) I&#x

Re: BTREE index: field ordering

On Fri, Mar 28, 2025 at 9:24 AM Ron Johnson wrote: > > "at least for WHERE conditions that are selective" confuses me. Aren't > _all_ WHERE clauses selective? > > >From earlier in the email, selectivity is a scale, the wording here implies "has a meaningful selectivity". "Fundamentally yes, but

Re: BTREE index: field ordering

sage if you are looking for the rare > false in a sea of trues but low selectivity if looking through those trues. > And to accommodate the highly-selective case a partial index is much better than indexing the low cardinality boolean column values. David J.

Re: BTREE index: field ordering

re than 5 fields, that are > matching by =, <>, LIKE and IN() > > I read that equality fields must be first, then the others. > > Is it correct? > > Fundamentally yes, but you also have to consider how selective the > conditions are. > Putting a column in the ind

Re: BTREE index: field ordering

nd IN() >  I read that equality fields must be first, then the others. >  Is it correct? Fundamentally yes, but you also have to consider how selective the conditions are. Putting a column in the index where the condition will only filter out few rows is not going to help; such rows should be omi

BTREE index: field ordering

7;WFR','BLQ','BLR','WFA','FRW','FRO','','0001'))   AND (tiporic IS NOT NULL)   AND (tiporic NOT LIKE '%cart%')     ) OR (   (tiporic LIKE '%cart%') AND

Re: ERROR: could not read block 0 in file when creating an index out of a function

Luca Ferrari writes: > On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov wrote: >> In your case `base/357283/365810` file is a new index file. For some >> reason Postgres tries to read the new index. I suppose this is because >> during reading the table `t` within the funct

Re: ERROR: could not read block 0 in file when creating an index out of a function

` file is a new index file. For some reason Postgres tries to read the new index. I suppose this is because during reading the table `t` within the function `f_t` it tries to access the new index. Yeah, even if it is not clear to me why it is trying to read the index that is under creation (i.e.,

Re: ERROR: could not read block 0 in file when creating an index out of a function

On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov wrote: > > I can reproduce this with the table `t` on PG 15.10. I didn't mention I'm running 16.6, but I'm pretty sure it is reproducible on other versions too. > > In your case `base/357283/365810` file is a new in

Re: ERROR: could not read block 0 in file when creating an index out of a function

an reproduce this with the table `t` on PG 15.10. In your case `base/357283/365810` file is a new index file. For some reason Postgres tries to read the new index. I suppose this is because during reading the table `t` within the function `f_t` it tries to access the new index. According to the d

ERROR: could not read block 0 in file when creating an index out of a function

Hi all, this is a little controversial, but hagin a function defined as immutable that selects a record out of a table, it is impossible to create an index over such function if the column has a constraint like a primary key. Here it is the use case: DROP TABLE if exists t; drop table if

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

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

Index usage with differing string types

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 the query, in some cases it isn't

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

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 <dmu...@gmail.com> 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

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

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 inde

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 n

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 > mo

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 unl

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 tuple

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

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 l

Index Partition Size Double of its Table Partition?

We're trying out a new non-unique covering (including) index on a couple of table partitions. We put the index on partitions for last month and this month. Both table partitions have similar sizes (45-46 GB) and row counts (330-333 million). The covering index on last month's partiti

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

On Tue, 15 Oct, 2024, 15:15 David Rowley, wrote: > On Sat, 12 Oct 2024 at 02:28, Durgamahesh Manne > wrote: > > Second column of composite index not in use effectively with index scan > when using second column at where clause > > > > I have composite index on (pl

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

On Sat, 12 Oct 2024 at 02:28, Durgamahesh Manne wrote: > Second column of composite index not in use effectively with index scan when > using second column at where clause > > I have composite index on (placedon,id) of test > When quering select * from test where id = '

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

,id) >>> In concurrent mode if i use id at where clause then query plan for that >>> id column changes >>> >>> How to mitigate it rather than use seperate index for id to continue >>> without change in query plan (index scan) during concurrent

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

t; id column changes >> >> How to mitigate it rather than use seperate index for id to continue >> without change in query plan (index scan) during concurrent activity >> > > Why the focus on "concurrent mode"? Perhaps explain what you mean by that. > > Spe

Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne wrote: > composite key (placedon,id) > In concurrent mode if i use id at where clause then query plan for that id > column changes > > How to mitigate it rather than use seperate index for id to continue > without change in quer

Fwd: Inefficient use of index scan on 2nd column of composite index during concurrent activity

-- Forwarded message - From: Durgamahesh Manne Date: Mon, Oct 7, 2024 at 10:01 AM Subject: Inefficient use of index scan on 2nd column of composite index during concurrent activity To: Hi team Second column of composite index not in use effectively with index scan when using

Re: Remedial C: Does an ltree GiST index *ever* set recheck to true?

eap. > > The comment in ltree_consistent is pretty definitive: > > /* All cases served by this function are exact */ > *recheck = false; > > > I wonder because an ltree GiST index is "lossy" and this behavior is more > > like a lossless strategy. I think that&

Re: Remedial C: Does an ltree GiST index *ever* set recheck to true?

false; > I wonder because an ltree GiST index is "lossy" and this behavior is more > like a lossless strategy. I think that's either because I've misunderstood > what "lossy" means in this case, or it's because ltree GiST index *pages *are > based on

Remedial C: Does an ltree GiST index *ever* set recheck to true?

I'm trying to determine if an ltree GiST index search *ever *needs to load a row out of heap for a recheck, of if the index entry itself includes enough information for a definitive answer. I believe that this is controlled by the recheck flag in the consistency function. >From what I&#x

Re: Does a partition key need to be part of a composite index for the planner to take advantage of it? (PG 16.3+)

> a new set of composite indexes that mirror the existing ones but include > organization_id as the first column in the composite index. When we create the > composite index to include organization ID in the first position, then the > planner > both selects the correct partitions,

Does a partition key need to be part of a composite index for the planner to take advantage of it? (PG 16.3+)

composite index. When we create the composite index to include organization ID in the first position, then the planner both selects the correct partitions, AND index scans those partitions. Is that expected behavior and it is appropriate to include any partition keys as leading columns in any indexes

Re: Issue while creating index dynamically

On Thu, Jul 25, 2024 at 7:42 AM veem v wrote: > I was thinking the individual statement will work fine if I pull out those > from the begin..end block, as those will then be not bounded by any outer > transaction. > However, When I was trying it from dbeaver by extracting indi

Re: Issue while creating index dynamically

want to understand , is > there > >> any alternate way to get away with this? > > > I'd write that in bash, not in a DO block. > > Yeah. I thought for a bit about using contrib/dblink to carry out > the commands in a different session, but I don't think that'

Re: Issue while creating index dynamically

> I'd write that in bash, not in a DO block. Yeah. I thought for a bit about using contrib/dblink to carry out the commands in a different session, but I don't think that'll work: CREATE INDEX CONCURRENTLY would think it has to wait out the transaction running the DO block a

Re: Issue while creating index dynamically

On Tue, Jul 23, 2024 at 4:10 PM veem v wrote: > Hi, > It's postgres version 15.4. We have a requirement to create an index on a > big partition table and want to do it online. And to make the script run in > an automated way on any day , through our ci/cd pipeline we were trying

Issue while creating index dynamically

Hi, It's postgres version 15.4. We have a requirement to create an index on a big partition table and want to do it online. And to make the script run in an automated way on any day , through our ci/cd pipeline we were trying to write it as below inside a begin/end block. I.e. create index

Re: How to create efficient index in this scenario?

On Sun, 9 Jun 2024 at 09:45, Lok P wrote: > > On Sat, Jun 8, 2024 at 7:03 PM veem v wrote: > >> >> There is a blog below (which is for oracle), showing how the index should >> be chosen and it states , "*Stick the columns you do range scans on >> last i

Re: How to create efficient index in this scenario?

gt; which we have to include the partition key as part of the primary key, so > it has to be a composite index. Either it has to be > (transaction_id,transaction_timestamp) or ( transaction_timestamp, > transaction_id). But which one should we go for, if both of the columns get > used

How to create efficient index in this scenario?

y, so it has to be a composite index. Either it has to be (transaction_id,transaction_timestamp) or ( transaction_timestamp, transaction_id). But which one should we go for, if both of the columns get used in all the queries? We will always be using transaction_timestamp as mostly a range pred

Re: Use of inefficient index in the presence of dead tuples

On Wed, 2024-05-29 at 14:36 +0200, Alexander Staubo wrote: > > On 29 May 2024, at 02:53, Tom Lane wrote: > > I'm unpersuaded by the idea that ANALYZE should count dead tuples. > > Since those are going to go away pretty soon, we would risk > > estimating on the basis of no-longer-relevant stats an

Re: Use of inefficient index in the presence of dead tuples

> On 29 May 2024, at 02:53, Tom Lane wrote: > > Alexander Staubo writes: >> (2) Set up schema. It's important to create the index before insertion, in >> order to provoke a >> situation where the indexes have dead tuples: >> ... >> (4

Re: Use of inefficient index in the presence of dead tuples

On Wed, 29 May 2024 at 12:53, Tom Lane wrote: > It would be interesting to see a non-artificial example that took > into account when the last auto-vacuum and auto-analyze really > happened, so we could see if there's any less-fragile way of > dealing with this situation. I think we need to find

Re: Use of inefficient index in the presence of dead tuples

Alexander Staubo writes: > (2) Set up schema. It's important to create the index before insertion, in > order to provoke a > situation where the indexes have dead tuples: > ... > (4) Then ensure all tuples are dead except one: > DELETE FROM outbox_batches; >

Re: Use of inefficient index in the presence of dead tuples

On Tue, May 28, 2024, 07:21 Alexander Staubo wrote: > > > I did explore a solution which is my “plan B” — adding a “done” column, > then using “UPDATE … SET done = true” rather than deleting the rows. This > causes dead tuples, of course, but then adding a new index with a “… W

Re: Use of inefficient index in the presence of dead tuples

On 28 May 2024, at 13:02, Laurenz Albe wrote: > ANALYZE considers only the live rows, so PostgreSQL knows that the query will > return only few results. So it chooses the smaller index rather than the one > that matches the WHERE condition perfectly. > > Unfortunately, it has

Re: Use of inefficient index in the presence of dead tuples

On Tue, 2024-05-28 at 10:00 +0200, Alexander Staubo wrote: > I am encountering an odd problem where Postgres will use the wrong index, > particularly if the table > has some dead tuples. The database affected is running 12.6, but I can also > reproduce with 16.3. > > To repr

Use of inefficient index in the presence of dead tuples

I am encountering an odd problem where Postgres will use the wrong index, particularly if the table has some dead tuples. The database affected is running 12.6, but I can also reproduce with 16.3. To reproduce: (1) Disable autovacuum. This is just so we can induce a scenario where there are

Re: Strange issue with unique index

On Thu, 2024-05-23 at 22:18 -0400, Tom Lane wrote: > writes: > > I've run into a strange issue with a unique index that I'm struggling to > > understand. I've extracted the basic info to reproduce this below. > > ... > > This will now block until session

RE: Strange issue with unique index

writes: >> I've run into a strange issue with a unique index that I'm struggling >> to understand. I've extracted the basic info to reproduce this below. >> ... >> This will now block until session 2 is complete. I don't understand >> why

Re: Strange issue with unique index

writes: > I've run into a strange issue with a unique index that I'm struggling to > understand. I've extracted the basic info to reproduce this below. > ... > This will now block until session 2 is complete. I don't understand why this > would block. I do know

Strange issue with unique index

Good day I've run into a strange issue with a unique index that I'm struggling to understand. I've extracted the basic info to reproduce this below. PG Version: 15.6 --Create structure CREATE SCHEMA IF NOT EXISTS playground; CREATE TABLE playground.parent ( p

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; > O

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. the

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

Using GIN Index to see if a nested key exists in JSONB

I have a table of semi-structured json that I am storing in a JSONB column with a GIN index: create table foo ( id text primary key, obj jsonb ); create index foo_obj on foo using gin (obj); I populated the table with 10,000 rows of randomly generated JSON objects, with

Re: subquery plan rows = 1, but it's merge joined instead of index lookup

rg Subject: subquery plan rows = 1, but it's merge joined instead of index lookup On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote: Is there some complexity limit after which the planner starts acting dumb? Yes, "join_collapse_limit" and "from_collapse_limit". You can t

Re: subquery plan rows = 1, but it's merge joined instead of index lookup

On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote: > Is there some complexity limit after which the planner starts acting dumb? Yes, "join_collapse_limit" and "from_collapse_limit". You can try increasing them. Yours, Laurenz Albe

Parallel GIN index?

Any plans for $subject? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com www.visena.com

Re: Seq scan vs index scan

Thanks Tom,David and Chris for detailed opinions Regards, Arun On Sat, 23 Mar 2024 at 09:25, arun chirappurath wrote: > Hi All, > > I have a table named users with index on user name. > > CREATE TABLE users ( > user_id SERIAL PRIMARY KEY, > username VARCHAR(50)

Re: Seq scan vs index scan

arun chirappurath writes: > I have a table named users with index on user name. > ... > When I try to do below select query it's taking seq scan and query returns > in 5ms. 5ms is an okay runtime, I would think. Is the table empty? > I am trying to force query to use

Re: Seq scan vs index scan

On Fri, Mar 22, 2024 at 8:55 PM arun chirappurath wrote: > > I am trying to force query to use indexes using query hints. > > Set enable indexscan to ON, > Same for bitmap and index only scan > Everything is on by default in the planner. You need to think in terms of what

Re: Seq scan vs index scan

> On Mar 22, 2024, at 20:55, arun chirappurath wrote: > I am trying to force query to use indexes using query hints. PostgreSQL does not have query hints. Enabling index scans using parameters doesn't *disable* other types of query nodes. You can disable sequential

Seq scan vs index scan

Hi All, I have a table named users with index on user name. CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, age INT ); CREATE INDEX idx_username ON users (username); When I try to do below select query it&#

Re: Efficient rows filter for array inclusion with gin index

which is supported by the gin index, the test for inclusion is fast and the query does not run a sequential scan over the whole "item_paths" table. However, because of the ARRAY[i2.ref_id] construct, it performs a sequential scan on i2. I was under the assumption that the ARRAY[] con

Efficient rows filter for array inclusion with gin index

; (uuid) that references their parent. In order to avoid recursive queries,there is a secondary table "item_paths" populated via triggers that have two columns "ref_id" (uuid that references a row in "items") and "item_path" (uuid[] which contains the path of

Re: B-tree index balance?

they would auto-balance *to a degree* during node splits, but all > > those "far-right corner" inserts still left them pretty lopsided. > > Thus, they provided a utility which we could use to determine the > > lopsidedness, and thus decide when to rebuild an index. &g

Re: B-tree index balance?

uot;far-right corner" inserts still left them pretty lopsided. > Thus, they provided a utility which we could use to determine the > lopsidedness, and thus decide when to rebuild an index. > Does Postgresql keep b-tree indexes on sequences fully balanced? If not, > how do I see how unba

B-tree index balance?

hem pretty lopsided. Thus, they provided a utility which we could use to determine the lopsidedness, and thus decide when to rebuild an index. Does Postgresql keep b-tree indexes on sequences fully balanced? If not, how do I see how unbalanced they are? (Assume PG12+.)

Order of multicolumn gist index

tps://www.postgresql.org/docs/current/indexes-multicolumn.html) states that "A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns." So I am trying to figure out the difference between the column

Re: Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix

Thank you for your response. I forgot to include it but we did check for that prior to our submission. We used this query to see how many indexes were related to the parent index via the inherits table: SELECT count(inh.inhrelid) FROM pg_class c inner join pg_inherits inh on c.oid = inh.inhparent

Re: Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix

On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote: > We have been utilizing partitioned tables with indexes. We've recently had an > issue > where the parent table's index (id, date) became invalid (indisvalid=FALSE, > indisready=FALSE in pg_index). For reference the paren

Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix

Hello all, We have been utilizing partitioned tables with indexes. We've recently had an issue where the parent table's index (id, date) became invalid (indisvalid=FALSE, indisready=FALSE in pg_index). For reference the parent table is partitioned on a date field within the table. I

Re: GIN INdex is not used with && operator for a text array index

balasubramanian c r writes: > when Operator '@>' is used the index is used and the execution time is 60ms. Yeah ... note that it's predicted to return just one row, and that guess is correct: > Bitmap Heap Scan on public.address18 (cost=261.25..262.52 rows=1 width=

GIN INdex is not used with && operator for a text array index

HI Team Sorry for the spam. We have Postgres DB where the list of addresses are stored and for a given complete address trigram of addresses are stored in a column which is a text array. after looking at the list of operators that are available for gin index I decided to use array_ops operator

Re: Understanding partial index selection

remember correctly, the table has around 50mil rows, and around 17mil of them should be included in the partial index due to the "where payload is not null" predicate. 0 deleted pages would be nicer than ~6k, but by my count, that's around 10% of the total index size. I also assume i

  1   2   3   4   5   6   7   8   9   10   >