public inbox for [email protected]help / color / mirror / Atom feed
Re: JSON Path and GIN Questions 12+ messages / 4 participants [nested] [flat]
* Re: JSON Path and GIN Questions @ 2023-09-14 04:41 Tom Lane <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tom Lane @ 2023-09-14 04:41 UTC (permalink / raw) To: Erik Rijkers <[email protected]>; +Cc: David E. Wheeler <[email protected]>; Erik Wienhold <[email protected]>; [email protected] Erik Rijkers <[email protected]> writes: > p 9/13/23 om 22:01 schreef David E. Wheeler: >> On Sep 13, 2023, at 01:11, Erik Rijkers <[email protected]> wrote: >>> "All use of json*() functions preclude index usage." >> Where did that come from? Why wouldn’t JSON* functions use indexes? I see that the docs only mention operators; why would the corresponding functions behave the same? > Sorry, perhaps my reply was a bit off-topic. > But you mentioned perhaps touching the docs and > the not-use-of-index is just so unexpected. Unexpected to who? I think the docs make it pretty plain that only operators on indexed columns are considered as index qualifications. Admittedly, 11.2 Index Types [1] makes the point only by not discussing any other case, but when you get to 11.10 Operator Classes and Operator Families [2] and discover that the entire index definition mechanism is based around operators not functions, you should be able to reach that conclusion. The point is made even more directly in 38.16 Interfacing Extensions to Indexes [3], though I'll concede that that's not material I'd expect the average PG user to read. As far as json in particular is concerned, 8.14.4 jsonb Indexing [4] is pretty clear about what is or is not supported. regards, tom lane [1] https://www.postgresql.org/docs/current/indexes-types.html [2] https://www.postgresql.org/docs/current/indexes-opclass.html [3] https://www.postgresql.org/docs/current/xindex.html [4] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-09-15 20:13 David E. Wheeler <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: David E. Wheeler @ 2023-09-15 20:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Erik Rijkers <[email protected]>; Erik Wienhold <[email protected]>; [email protected] On Sep 14, 2023, at 00:41, Tom Lane <[email protected]> wrote: > As far as json in particular is concerned, 8.14.4 jsonb Indexing [4] > is pretty clear about what is or is not supported. How do you feel about this note, then? diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index b6c2ddbf55..7dda727f0d 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -413,6 +413,13 @@ SELECT doc->'site_name' FROM websites Two GIN <quote>operator classes</quote> are provided, offering different performance and flexibility trade-offs. </para> + <note> + <para> + As with all indexes, only operators on indexed columns are considered as + index qualifications. In other words, only <type>jsonb</type> operators can + take advantage of GIN indexes; <type>jsonb</type> functions cannot. + </para> + </note> <para> The default GIN operator class for <type>jsonb</type> supports queries with the key-exists operators <literal>?</literal>, <literal>?|</literal> Best, David Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-09-15 21:14 Tom Lane <[email protected]> parent: David E. Wheeler <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tom Lane @ 2023-09-15 21:14 UTC (permalink / raw) To: David E. Wheeler <[email protected]>; +Cc: Erik Rijkers <[email protected]>; Erik Wienhold <[email protected]>; [email protected] "David E. Wheeler" <[email protected]> writes: > On Sep 14, 2023, at 00:41, Tom Lane <[email protected]> wrote: >> As far as json in particular is concerned, 8.14.4 jsonb Indexing [4] >> is pretty clear about what is or is not supported. > How do you feel about this note, then? I think it's unnecessary. If we did consider it necessary, why wouldn't just about every subsection in chapter 8 need similar wording? regards, tom lane ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-09-16 17:43 David E. Wheeler <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: David E. Wheeler @ 2023-09-16 17:43 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Sep 15, 2023, at 20:36, Tom Lane <[email protected]> wrote: > I think that that indicates that you're putting the info in the > wrong place. Perhaps the right answer is to insert something > more explicit in section 11.2, which is the first place where > we really spend any effort discussing what can be indexed. Fair enough. How ’bout this? --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -120,7 +120,7 @@ CREATE INDEX test1_id_index ON test1 (id); B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link linkend="bloom">bloom</link>. Each index type uses a different - algorithm that is best suited to different types of queries. + algorithm that is best suited to different types of queries and operators. By default, the <link linkend="sql-createindex"><command>CREATE INDEX</command></link> command creates B-tree indexes, which fit the most common situations. @@ -132,6 +132,14 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> </programlisting> </para> + <note> + <para> + Only operators on indexed columns are considered as index qualifications. + Functions never qualify for index usage, aside from + <link linkend="indexes-expressional">indexes on expressions</link>. + </para> + </note> + <sect2 id="indexes-types-btree"> <title>B-Tree</title> Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-09-17 16:20 Tom Lane <[email protected]> parent: David E. Wheeler <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tom Lane @ 2023-09-17 16:20 UTC (permalink / raw) To: David E. Wheeler <[email protected]>; +Cc: [email protected] "David E. Wheeler" <[email protected]> writes: > On Sep 15, 2023, at 20:36, Tom Lane <[email protected]> wrote: >> I think that that indicates that you're putting the info in the >> wrong place. Perhaps the right answer is to insert something >> more explicit in section 11.2, which is the first place where >> we really spend any effort discussing what can be indexed. > Fair enough. How ’bout this? After thinking about it for awhile, I think we need some more discursive explanation of what's allowed, perhaps along the lines of the attached. (I still can't shake the feeling that this is duplicative; but I can't find anything comparable until you get into the weeds in Section V.) I put the new text at the end of section 11.1, but perhaps it belongs a little further up in that section; it seems more important than some of the preceding paras. regards, tom lane Attachments: [text/x-diff] v1-document-indexable-clauses-better.patch (2.2K, ../../[email protected]/2-v1-document-indexable-clauses-better.patch) download | inline diff: diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 55122129d5..1a0b003fb0 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -109,6 +109,39 @@ CREATE INDEX test1_id_index ON test1 (id); Therefore indexes that are seldom or never used in queries should be removed. </para> + + <para> + In general, <productname>PostgreSQL</productname> indexes can be used + to optimize queries that contain one or more <literal>WHERE</literal> + or <literal>JOIN</literal> clauses of the form + +<synopsis> +<replaceable>indexed-column</replaceable> <replaceable>indexable-operator</replaceable> <replaceable>comparison-value</replaceable> +</synopsis> + + Here, the <replaceable>indexed-column</replaceable> is whatever + column or expression the index has been defined on. + The <replaceable>indexable-operator</replaceable> is an operator that + is a member of the index's <firstterm>operator class</firstterm> for + the indexed column. (More details about that appear below.) + And the <replaceable>comparison-value</replaceable> can be any + expression that is not volatile and does not reference the index's + table. + </para> + + <para> + In some cases the query planner can extract an indexable clause of + this form from another SQL construct. A simple example is that if + the original clause was + +<synopsis> +<replaceable>comparison-value</replaceable> <replaceable>operator</replaceable> <replaceable>indexed-column</replaceable> +</synopsis> + + then it can be flipped around into indexable form if the + original <replaceable>operator</replaceable> has a commutator + operator that is a member of the index's operator class. + </para> </sect1> @@ -120,7 +153,7 @@ CREATE INDEX test1_id_index ON test1 (id); B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link linkend="bloom">bloom</link>. Each index type uses a different - algorithm that is best suited to different types of queries. + algorithm that is best suited to different types of indexable clauses. By default, the <link linkend="sql-createindex"><command>CREATE INDEX</command></link> command creates B-tree indexes, which fit the most common situations. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-09-17 22:09 David E. Wheeler <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: David E. Wheeler @ 2023-09-17 22:09 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Sep 17, 2023, at 12:20, Tom Lane <[email protected]> wrote: > After thinking about it for awhile, I think we need some more > discursive explanation of what's allowed, perhaps along the lines > of the attached. (I still can't shake the feeling that this is > duplicative; but I can't find anything comparable until you get > into the weeds in Section V.) > > I put the new text at the end of section 11.1, but perhaps it > belongs a little further up in that section; it seems more > important than some of the preceding paras. I think this is useful, but also that it’s worth calling out explicitly that functions do not count as indexable operators. True by definition, of course, but I at least had assumed that since an operator is, in a sense, syntax sugar for a function call, they are in some sense the same thing. A header might be useful, something like “What Counts as an indexable expression”. Best, David Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-12-17 17:55 David E. Wheeler <[email protected]> parent: David E. Wheeler <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: David E. Wheeler @ 2023-12-17 17:55 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Sep 17, 2023, at 18:09, David E. Wheeler <[email protected]> wrote: > I think this is useful, but also that it’s worth calling out explicitly that functions do not count as indexable operators. True by definition, of course, but I at least had assumed that since an operator is, in a sense, syntax sugar for a function call, they are in some sense the same thing. > > A header might be useful, something like “What Counts as an indexable expression”. Hey Tom, are you still thinking about adding this bit to the docs? I took a quick look at master and didn’t see it there. Thanks, David ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-12-17 21:08 Tom Lane <[email protected]> parent: David E. Wheeler <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tom Lane @ 2023-12-17 21:08 UTC (permalink / raw) To: David E. Wheeler <[email protected]>; +Cc: [email protected] "David E. Wheeler" <[email protected]> writes: > Hey Tom, are you still thinking about adding this bit to the docs? I took a quick look at master and didn’t see it there. I'd waited because the discussion was still active, and then it kind of slipped off the radar. I'll take another look and push some form of what I suggested. That doesn't really address the jsonpath oddities you were on about, though. regards, tom lane ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JSON Path and GIN Questions @ 2023-12-17 23:30 David E. Wheeler <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: David E. Wheeler @ 2023-12-17 23:30 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Dec 17, 2023, at 16:08, Tom Lane <[email protected]> wrote: > I'd waited because the discussion was still active, and then it > kind of slipped off the radar. I'll take another look and push > some form of what I suggested. Right on. > That doesn't really address the > jsonpath oddities you were on about, though. No, I attempted to address those in [a patch][1]. [1]: https://commitfest.postgresql.org/45/4624/ Best, David ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Fix data checksum processing for temp relations and dropped databases @ 2026-07-10 00:12 Fujii Masao <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Fujii Masao @ 2026-07-10 00:12 UTC (permalink / raw) To: Daniel Gustafsson <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected] On Thu, Jul 9, 2026 at 4:56 PM Daniel Gustafsson <[email protected]> wrote: > I like this suggestion, though I think it should go after the call to > pgstat_progress_update_param since the db in question reached ProcessDatabase. > I would add it as an else-if case to the conditional testing we have for > _FAILED and _ABORTED. Thanks for the review! I've updated the patch based on your and Horiguchi-san's suggestions. Regards, -- Fujii Masao Attachments: [application/octet-stream] v2-0001-Fix-data-checksum-processing-for-temp-relations-a.patch (3.8K, ../../CAHGQGwGaXoAPO814Hn87edzegW53wVTR0RoPPBdHCR43f03FGw@mail.gmail.com/2-v2-0001-Fix-data-checksum-processing-for-temp-relations-a.patch) download | inline diff: From bdb49627b59a24ecefaf308f2dd782b25a6dcca9 Mon Sep 17 00:00:00 2001 From: Fujii Masao <[email protected]> Date: Wed, 8 Jul 2026 23:52:39 +0900 Subject: [PATCH v2] Fix data checksum processing for temp relations and dropped databases When building the list of temporary relations to wait for, the code previously included temporary relations without storage, such as temporary views, even though they are irrelevant to checksum processing. As a result, enabling data checksums could wait for a long-lived session that owned only a temporary view. This commit fixes the issue by filtering temporary relations with storage only, matching the existing behavior for non-temporary relations. Also, when enabling data checksums online, the launcher assigns the first worker to process shared catalogs and prevents later workers from doing so. Previously, if that worker's database was dropped after it had been selected for processing but before checksum processing began, the worker failed without processing the shared catalogs, yet they were still marked as processed. As a result, later workers skipped them, and checksum enabling could complete successfully even though the shared catalogs had never been processed. This commit fixes the issue by marking shared catalogs as processed only after a worker completes successfully. Author: Fujii Masao <[email protected]> Reviewed-by: Kyotaro Horiguchi <[email protected]> Reviewed-by: Daniel Gustafsson <[email protected]> Discussion: https://postgr.es/m/CAHGQGwGDHAQw=bmpRzk+EmKzVtxZiD5YDurMUffBMwr6WXugQA@mail.gmail.com --- src/backend/postmaster/datachecksum_state.c | 24 ++++++++++++++------- 1 file changed, 16 insertions(+), 8 deletions(-) diff --git a/src/backend/postmaster/datachecksum_state.c b/src/backend/postmaster/datachecksum_state.c index 68557c16cb9..6b136a86b74 100644 --- a/src/backend/postmaster/datachecksum_state.c +++ b/src/backend/postmaster/datachecksum_state.c @@ -1324,6 +1324,14 @@ ProcessAllDatabases(void) /* Abort flag set, so exit the whole process */ return false; } + else if (result == DATACHECKSUMSWORKER_DROPDB) + { + /* + * Ignore databases that were dropped before their worker could + * process them, and continue with the remaining databases. + */ + continue; + } /* * When one database has completed, it will have done shared catalogs @@ -1469,11 +1477,11 @@ FreeDatabaseList(List *dblist) * Compile a list of relations in the database * * Returns a list of OIDs for the requested relation types. If temp_relations - * is True then only temporary relations are returned. If temp_relations is - * False then non-temporary relations which have data checksums are returned. - * If include_shared is True then shared relations are included as well in a - * non-temporary list. include_shared has no relevance when building a list of - * temporary relations. + * is True then only temporary relations with storage are returned. If + * temp_relations is False then non-temporary relations with storage are + * returned. If include_shared is True then shared relations are included as + * well in a non-temporary list. include_shared has no relevance when building + * a list of temporary relations. */ static List * BuildRelationList(bool temp_relations, bool include_shared) @@ -1494,6 +1502,9 @@ BuildRelationList(bool temp_relations, bool include_shared) { Form_pg_class pgc = (Form_pg_class) GETSTRUCT(tup); + if (!RELKIND_HAS_STORAGE(pgc->relkind)) + continue; + /* Only include temporary relations when explicitly asked to */ if (pgc->relpersistence == RELPERSISTENCE_TEMP) { @@ -1509,9 +1520,6 @@ BuildRelationList(bool temp_relations, bool include_shared) if (temp_relations) continue; - if (!RELKIND_HAS_STORAGE(pgc->relkind)) - continue; - if (pgc->relisshared && !include_shared) continue; } -- 2.55.0 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Fix data checksum processing for temp relations and dropped databases @ 2026-07-10 08:32 Daniel Gustafsson <[email protected]> parent: Fujii Masao <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Daniel Gustafsson @ 2026-07-10 08:32 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected] > On 10 Jul 2026, at 02:12, Fujii Masao <[email protected]> wrote: > > On Thu, Jul 9, 2026 at 4:56 PM Daniel Gustafsson <[email protected]> wrote: >> I like this suggestion, though I think it should go after the call to >> pgstat_progress_update_param since the db in question reached ProcessDatabase. >> I would add it as an else-if case to the conditional testing we have for >> _FAILED and _ABORTED. > > Thanks for the review! I've updated the patch based on > your and Horiguchi-san's suggestions. +1, v2 LGTM. -- Daniel Gustafsson ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Fix data checksum processing for temp relations and dropped databases @ 2026-07-10 13:38 Fujii Masao <[email protected]> parent: Daniel Gustafsson <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Fujii Masao @ 2026-07-10 13:38 UTC (permalink / raw) To: Daniel Gustafsson <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected] On Fri, Jul 10, 2026 at 5:32 PM Daniel Gustafsson <[email protected]> wrote: > > Thanks for the review! I've updated the patch based on > > your and Horiguchi-san's suggestions. > > +1, v2 LGTM. Thanks for the review! I've pushed the patch. Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-07-10 13:38 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-09-14 04:41 Re: JSON Path and GIN Questions Tom Lane <[email protected]> 2023-09-15 20:13 ` David E. Wheeler <[email protected]> 2023-09-15 21:14 ` Tom Lane <[email protected]> 2023-09-16 17:43 ` David E. Wheeler <[email protected]> 2023-09-17 16:20 ` Tom Lane <[email protected]> 2023-09-17 22:09 ` David E. Wheeler <[email protected]> 2023-12-17 17:55 ` David E. Wheeler <[email protected]> 2023-12-17 21:08 ` Tom Lane <[email protected]> 2023-12-17 23:30 ` David E. Wheeler <[email protected]> 2026-07-10 00:12 Re: Fix data checksum processing for temp relations and dropped databases Fujii Masao <[email protected]> 2026-07-10 08:32 ` Re: Fix data checksum processing for temp relations and dropped databases Daniel Gustafsson <[email protected]> 2026-07-10 13:38 ` Re: Fix data checksum processing for temp relations and dropped databases Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox