Re: Get info about the index
Adrian, On Fri, Aug 1, 2025 at 8:13 PM Adrian Klaver wrote: > > On 8/1/25 17:06, Igor Korot wrote: > > Hi, Laurenz, > > > > > I'm looking at the pg_index table and I see it has: > > > > [quote] > > indisexclusion bool > > > > If true, this index supports an exclusion constraint > > [/quote] > > > > If I read the docs correctly, this field indicates whether the > > WHERE condition is actually present. > > > > Am I right? > > No, it refers to: > > https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION Thx for clarification. But is there a field that I'm referencing above? Thank you. > > > > > Thank you. > > > >> > >> Yours, > >> Laurenz Albe > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
Re: Get info about the index
On 8/1/25 20:50, Igor Korot wrote: Adrian, If I read the docs correctly, this field indicates whether the WHERE condition is actually present. Am I right? No, it refers to: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION Thx for clarification. But is there a field that I'm referencing above? This was answered here: https://www.postgresql.org/message-id/2fad4427bff83bc73f9b4215e284a76343f046f2.camel%40cybertec.at Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Get info about the index
On 8/1/25 17:06, Igor Korot wrote: Hi, Laurenz, I'm looking at the pg_index table and I see it has: [quote] indisexclusion bool If true, this index supports an exclusion constraint [/quote] If I read the docs correctly, this field indicates whether the WHERE condition is actually present. Am I right? No, it refers to: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION Thank you. Yours, Laurenz Albe -- Adrian Klaver adrian.kla...@aklaver.com
Re: Get info about the index
On Fri, Aug 1, 2025 at 8:06 PM Igor Korot wrote: > > If I read the docs correctly, this field indicates whether the > WHERE condition is actually present. > Are you referring to the condition on a partial index? You can get that using pg_get_expr(): select pg_get_expr(indpred, indrelid) from pg_index where indexrelid = 'my_partial_index'::regclass; - Jon
Re: Get info about the index
Hi, Laurenz,
On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe wrote:
>
> On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:
> > SELECT
> > t.relname AS table_name,
> > i.relname AS index_name,
> > a.attname AS column_name
> > FROM
> > pg_class t,
> > pg_class i,
> > pg_index ix,
> > pg_attribute a
> > WHERE
> > t.oid = ix.indrelid AND
> > i.oid = ix.indexrelid AND
> > a.attrelid = t.oid AND
> > a.attnum = ANY(ix.indkey) AND
> > t.relkind = 'r' AND -- 'r' for regular table
> > t.relname = 'your_table_name' -- Optional: filter by table name
> > ORDER BY
> > t.relname,
> > i.relname,
> > a.attnum;
> >
> > I can build on top of this query, however I have 2 issues:
> >
> > First and most important one - they are filtering by just table name.
> > How can I filter by the fully qualified name - catalog.schema.table?
>
> "catalog" is irrelevant, since PostgreSQL doesn't allow cross-database
> queries.
>
> To add a filter for the schema, use
>
>AND t.relnamespace = 'schemaname'::regnamespace
>
> > Second - how cn I get the partial index condition? Either the whole
> > WHERE clause (which I will have to parse)
> > or the broken down one (field, condition {AND|OR} field, condition}?
>
>SELECT pg_get_expr(ix.indpred, t.oid)
I'm looking at the pg_index table and I see it has:
[quote]
indisexclusion bool
If true, this index supports an exclusion constraint
[/quote]
If I read the docs correctly, this field indicates whether the
WHERE condition is actually present.
Am I right?
Thank you.
>
> Yours,
> Laurenz Albe
Re: Failing to allocate memory when I think it shouldn't
## Siraj G (tosira...@gmail.com): > I am getting the same error in postgres 12 (sorry that our version upgrade > sucks). In all likelyhood, this is a somewhat different situation, as nothing here points to JIT. > I see that hash_mem_multiplier is available from version 13. What > could we do in version 12? Obviously, you could upgrade - you already identified the change which could at least improve matters here (when you do that, don't just stop at version 13: that is going EOL in a few months, too; and the whole hash memory allocation got some more polishing in 15). Also, as you got a standard out-of-memory error and not the dreaded OOM-kill, I assume that you set vm.overcommit_memory to a non-default (that is, != 0) value, but you did not mention anything about setting overcommit_ratio or overcommit_bytes - see upthread for a link to the documentation. Make sure that you have reasonable settings here. Thirdly, you should check whether you memory settings are actually suitable for your workload and machine, considering concurrent operations and their memory usage against actually available memory. Then check if that specific hash join grows beyond reasonable size (can you even execute the statement with the given parameters on an idle system?), and if it does investigate why the hash map is so much larger than estimated. You might have a statistics problem. You could try to reduce work_mem to steer the planner away from that hash join to a less memory-intensive strategy, or you could temporarily disable hash joins all together and analyse the impact on your application. Regards, Christoph -- Spare Space
