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

Re: Understanding partial index selection

On 11/28/23 18:13, Owen Nelson wrote: > Aurora is not really Postgres Oh geez, I didn't realize there was such a divide. This is my first look at Aurora and I thought it was just a hosted postgres offering. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html

Re: Understanding partial index selection

> Aurora is not really Postgres Oh geez, I didn't realize there was such a divide. This is my first look at Aurora and I thought it was just a hosted postgres offering. Still, I'll take what I can get. Hopefully, some of this will carry over.

Re: Understanding partial index selection

Owen Nelson writes: >> Are your ANALYZE stats up to date on this table? > It's a very good question! Right now, I'm taking it on faith that > autovacuum and autoanalyze are keeping things up to date, but if I'm honest > I've been getting some conflicting information from pg_stat_user_tables and >

Re: Understanding partial index selection

; "fk_messagedestination_msg_id_message" FOREIGN KEY (msg_id) REFERENCES > message(id) ON DELETE CASCADE > ``` > > I periodically run a query like this: > ``` > UPDATE message SET payload = NULL WHERE id IN ( > SELECT id FROM message > WHERE > payload IS

Re: Understanding partial index selection

-> Seq Scan on message message_1 > (cost=0.00..2050554.24 rows=6175068 width=38) (actual > time=8424.977..20880.945 rows=65 loops=1) > Filter: ((payload IS NOT NULL) AND > (expiration <= now())) >

Re: Understanding partial index selection

Owen Nelson writes: > The hope is the sub-select would leverage the index > "message_payload_not_null_pidx" but when I `EXPLAIN ANALYZE` the query, I > see a seq scan instead. I think your problem is the horrid rowcount misestimation here: >

Understanding partial index selection

LECT id FROM message WHERE payload IS NOT NULL AND expiration <= now() LIMIT 5000 FOR UPDATE SKIP LOCKED ); ``` The aim is to cull payload values after the retention period has expired. The hope is the sub-select would leverage the index "message_payload_not_null_p

Re: Parallel Index Scan Implementation

On Mon, Nov 27, 2023 at 4:11 AM Brajendra Pratap Singh < singh.bpratap...@gmail.com> wrote: > Hi Postgresql Experts, > > We have a basic requirement where we need to implement the parallel index > scan instead of parallel seq scan at table level. Please suggest the bes

Parallel Index Scan Implementation

Hi Postgresql Experts, We have a basic requirement where we need to implement the parallel index scan instead of parallel seq scan at table level. Please suggest the best way to do this. Postgresql DB Version : 12.6 Table size : 2-5 GB OS : Centos-7 RAM : 96 GB CPU : 32 shared_buffer=20GB

Re: BRIN index maintenance on table without primary key

On Fri, 27 Oct 2023, Dimitrios Apostolou wrote: So the question is: how to maintain the physical order of the tuples? Answering to myself, there doesn't seem to be any way to run pg_repack on a table without a UNIQUE key. To run CLUSTER, the only way I see is to: 1. Create a btree ind

BRIN index maintenance on table without primary key

Hello list. Key characteristics of my case: + HUGE table with 20G rows, ca 2TB + May be partitioned (have both versions on two test clusters ATM) + Plenty of inserts, no updates + No primary key - we had one IDENTITY bigint column until recently, but it proved useless and inefficient (index

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

Index based search have issues with 11.20

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.16 to 11.20 The particular database runs on

GIST index and ORDER BY

of TOP N ordered by operation date DESC (Ie. latest operations meeting search criteria) I’ve decided to have a single “universal” index that will cover all search criteria: multicolumn GIST with btree_gist extension. (Multiple indexes don’t work well because selectivity of subsets of columns is

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 >

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 mo

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 >

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

E clause from the "bikes" subquery is removed then first Index Scan on dealers_pk is made and then Bitmap Index Scans on "bikes" and "cars" table using indexes on "dealer_name" columns. --- -- Avai

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

E clause from the "bikes" subquery is removed then first Index Scan on dealers_pk is made and then Bitmap Index Scans on "bikes" and "cars" table using indexes on "dealer_name" columns. --- -- Avai

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

E clause from the "bikes" subquery is removed then first Index Scan on dealers_pk is made and then Bitmap Index Scans on "bikes" and "cars" table using indexes on "dealer_name" columns. --- -- Avai

Index scan is not pushed down to union all subquery

icle, id, dealer_name FROM cars -- In the real use case I have here dozens of tables ) SELECT dealers.name dealer, targets.vehicle, targets.id FROM dealers JOIN targets ON dealers.name = targets.dealer_name WHERE dealers.id in (54,12,456,315,468) If the WHERE clause from the

[Beginner Question]What should I do if I want to achieve lsm-tree index in postgresql?

Hi community, I am a student and I want to achieve lsm-tree index in postgresql, for my database experiment, I have already read the document of postgresql, and some postgresql's moudle source, but I really don't know where to start this work. Make a new extension like 'https://gi

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

On 9/7/23 23:51, Sai Teja wrote: Thank you so much for all your responses. I just tried with Hash, GIN etc But it didn't worked. And I think it is because of "Xpath" expression which I used in the index create command. But is there any alternative way to change this Xpath?

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

tead of one that must produce > the whole Document, > just so a few elements are picked out of it? FWIW. --DD > If push comes to shove, the streaming-based extraction can be done outside the DB, stored in a new column or table, and index that instead. This is in fact exactly the approach I t

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

On Thu, Sep 7, 2023 at 10:22 PM Tom Lane wrote: > Erik Wienhold writes: > > Looks like "Huge input lookup" as reported in [1] (also from Sai) and > that > > error is from libxml. > > Ah, thanks for the pointer. It looks like for the DOCUMENT case, > we could maybe relax this restriction by pass

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

Thank you so much for all your responses. I just tried with Hash, GIN etc But it didn't worked. And I think it is because of "Xpath" expression which I used in the index create command. But is there any alternative way to change this Xpath? Since I need to parse the XML as th

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

Erik Wienhold writes: > On 07/09/2023 21:09 CEST Tom Lane wrote: >> There is no such string anywhere in the Postgres source code; >> furthermore, if someone tried to add such an error, it'd get rejected >> (I hope) as not conforming to our style guidelines. I thought maybe >> it's coming from li

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

On Thu, Sep 7, 2023 at 12:28 PM Sai Teja wrote: > Here we’re using Xpath expression to create the index since postgreSQL > directly does not support comparison methods. So, we decided to use Xpath > expression. But while creating the index as I mentioned above we’re facing > the iss

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

On 07/09/2023 21:09 CEST Tom Lane wrote: > Sai Teja writes: > > We have one table which is storing XML data with 30k records and stores > > huge amount of data. > > We are trying to create the index for this column in the table. But, we’re > > getting “Huge input Lo

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

Sai Teja writes: > We have one table which is storing XML data with 30k records and stores > huge amount of data. > We are trying to create the index for this column in the table. But, we’re > getting “Huge input Lookup error” during creation of Index. There is no such string any

Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

Hi All, We have one table which is storing XML data with 30k records and stores huge amount of data. We are trying to create the index for this column in the table. But, we’re getting “Huge input Lookup error” during creation of Index. Please check the below command which is used to create the

Re: To create or not to create that index

Ah no it is not. Something else was changed at the same time. Sigh. Thanks for clarifying David On Fri, Aug 18, 2023 at 10:42 AM Stefan Knecht wrote: > But that "invalid" index is being used by queries > > On Fri, Aug 18, 2023 at 10:41 AM David G. Johnston < > d

Re: To create or not to create that index

On Thu, Aug 17, 2023 at 8:43 PM Stefan Knecht wrote: > But that "invalid" index is being used by queries > >> >> Please don't top-post. If it is used by queries it isn't invalid and thus its existence shouldn't be surprising. So I'm not sure what you are saying. David J.

Re: To create or not to create that index

But that "invalid" index is being used by queries On Fri, Aug 18, 2023 at 10:41 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht > wrote: > >> Hello >> >> Why does this happen? >>

Re: To create or not to create that index

On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht wrote: > Hello > > Why does this happen? > > profile_aggregates=> create index concurrently foo_idx on agg (status, > foots, created_ts); > > > ^CCancel request sent > ERROR: canceling statement due to

To create or not to create that index

Hello Why does this happen? profile_aggregates=> create index concurrently foo_idx on agg (status, foots, created_ts); ^CCancel request sent ERROR: canceling statement due to user request profile_aggregates=> profile_aggregates=> create index concurrently foo_idx on agg (stat

Re: PgSQL 15.3: Execution plan not using index as expected

Thanks Rob, no it's not a problem with the index. It's a problem with the use of CURRENT_USER in the WHERE I submitted a new post on this matter with a test case. Kind regards == Dürr Software Entw. Guggenberg 26, DE-82380 Peißenberg fon: +49-88

Re: PgSQL 15.3: Execution plan not using index as expected

NT_USER) the planner works as expected.. The old 9.x - version of PgSQL didn't have that problem. Test case: -- our test table with index on user_id CREATE TABLE tt (  user_id VARCHAR(63) NOT NULL DEFAULT SESSION_USER ); CREATE INDEX tt_user_id_idx ON tt(user_id); -- fill with test data INSERT I

Re: PgSQL 15.3: Execution plan not using index as expected

) 4.8.3, 64-bit > > > - > >   PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu, > compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit > (1 Zeile) > > 9.3 plan -> Index Scan using client_session_user_id_idx on client_sess

Re: PgSQL 15.3: Execution plan not using index as expected

3 ms, Emission 29.624 ms, Total 228.924 ms Execution Time: 6001.014 ms (18 Zeilen) Funny thing: if i create an index on tstamp_start alone, it is used just perfectly: Indexe:     "client_session_pkey" PRIMARY KEY, btree (id)     "client_session_tstamp_start" btree (tstamp_st

Re: PgSQL 15.3: Execution plan not using index as expected

On 8/9/23 01:14, Dürr Software wrote: Dear list, i have a strange problem when migrating a DB from version 9.3.4 to 15.3: An index which seems perfect for the query and is used in 9.3.4 as expected is not used in 15.3. Did you run ANALYZE on the 15.3 database after the migration? -- Adrian

PgSQL 15.3: Execution plan not using index as expected

Dear list, i have a strange problem when migrating a DB from version 9.3.4 to 15.3: An index which seems perfect for the query and is used in 9.3.4 as expected is not used in 15.3. I just wonder, whether the bug is on my side or on PgSQL's.. The de

Re: Query take a long time and use no index

uld express this as: SELECT COUNT(*) FROM (SELECT DISTINCT DATE_TRUNC('day', TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data WHERE channel_id = 5 AND timestamp >= 0) a; If there was an index on (channel_id, (DATE_TRUNC('day', TIMESTAMP &

Re: Query take a long time and use no index

On Tue, 18 Jul 2023 at 06:19, basti wrote: > > Thanks a lot tomas, i will try it. > > I have find out that there is a 'aggregation' function in the frontend. > But this is MySQL specific and I have no idea the transform it to postgres. > > It looks like: > 'REPLACE INTO aggregate (channel_id, type

Re: Query take a long time and use no index

Thanks a lot tomas, i will try it. I have find out that there is a 'aggregation' function in the frontend. But this is MySQL specific and I have no idea the transform it to postgres. It looks like: 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) SELECT channel_id, ? AS type

Re: Query take a long time and use no index

| indexdef ---++--  data  | idx_16391_idx_adf3f36372f5a1aa | CREATE INDEX idx_16391_idx_adf3f36372f5a1aa ON volkszaehler.data USING btree (channel_id)  data  | idx_16391_primary  | CREATE UN

Re: Query take a long time and use no index

On 7/17/23 13:20, Tomas Vondra wrote: > ... > > It's always going to be slow with the COUNT(DISTINCT), I'm afraid. > > Not sure how much you can modify the query / database, and how accurate > results you need. If you're OK with estimates, you can try postgres-hll > extension [2] which estimates

Re: Query take a long time and use no index

247 > (1 row) > > > The Indexes: > > volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE > tablename LIKE 'data%' ORDER BY tablename,indexname; > >  tablename |   indexname    |   indexdef > ---++------

  1   2   3   4   5   6   7   8   9   10   >