public inbox for [email protected]
help / color / mirror / Atom feedFrom: Guillaume Lelarge <[email protected]>
To: jian he <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Michael Christofides <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
Date: Wed, 11 Dec 2024 21:29:36 +0100
Message-ID: <CAECtzeXsJbONQDGu1dC9f_RO2WLFK2ZwPNrFKTvYMbWE+w-oAQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxGF6fg4+YH8sRTf0d7E-84ZCPy2rUivS=yAWAKYyJ9+mg@mail.gmail.com>
References: <CANNMO++9W_UA8PB9AkDRF3JBv4K7K1FnisrXTjicL+WZhwUJzw@mail.gmail.com>
<CA+TgmoZF7PbO7DB_VMQS4664EgOdObhFtAoLUb83bp3i-623UQ@mail.gmail.com>
<CANNMO++d9CKuJEzSnRWFCHcmKr2DGASTtEMZ9bpEYHSa4QFQvA@mail.gmail.com>
<[email protected]>
<CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com>
<CAKFQuwbLq3GWoJw-zNa43pkcfg5gh4gGQKMptQtp4qX_nQL9AQ@mail.gmail.com>
<CAApHDvrYwEt=ueMTn39okNihZffhKgqvxedLqDJu=hmSsZ=g9Q@mail.gmail.com>
<CAFwT4nC5255GkSt1WEfTHi6cwUuY4fjUmH15nD91c1JvNoOxNA@mail.gmail.com>
<CAECtzeUKbyZNuFRu4DTr_3p44egDXGbPpGonixqJninWXyBvnQ@mail.gmail.com>
<CA+TgmobY4N3QYWTW4bj9K=gG3Q4rTAsZLvi_=ODZYRBpk=ZVNA@mail.gmail.com>
<CAECtzeVvrjxXmE3TB9zz3MD_RwiE=MCg2Xjx0HXoSn4e-VRSOg@mail.gmail.com>
<CAECtzeWpusNrZADkQ01ZZL_fFtOWtwtnw_uixqy9hgK0qUCScQ@mail.gmail.com>
<CA+TgmoZO2HtDGHf7K4rmNdfUGuS=ngsPgwrH=JdyF6Okcnzfkw@mail.gmail.com>
<CAECtzeW=yo4H-G8rBheWwiET-e+PaBpOHHaL9Yu0JpYTd557_Q@mail.gmail.com>
<CAKAnmmLOkvJ85MGyouFBr2tS+0TSZ87=t=8iaZKU25WG98nZaw@mail.gmail.com>
<CAECtzeUwpK5gNWQqoK4ZjwXANtdE2fWirJyaBDqkytPzfWZqzg@mail.gmail.com>
<CAKAnmm+QQP-dTkjsDVbEcphrdXqCTq-ALTCMUwPaYWZmqUp=NQ@mail.gmail.com>
<CAECtzeVkkmvmHWVOTzwpX_GX+k4h7EWAyfERqes9C0z4pzMN2A@mail.gmail.com>
<CAApHDvpj0Cj6Z8VnDN5htzF24b4_hKL+1_n=HOehLHkBK_E_TQ@mail.gmail.com>
<CAECtzeUWiA+FNq0qGp94Ez4UAwbEZyB7avTqKDs6wkPhU56mpw@mail.gmail.com>
<CAApHDvqjmr=CZ2M57eErxn2Ubcb0d2EmppZhTj8k==Ud_eRfoQ@mail.gmail.com>
<CAApHDvqLdzgz77JsE-yTki3w9UiKQ-uTMLRctazcu+99-ips3g@mail.gmail.com>
<CACJufxGF6fg4+YH8sRTf0d7E-84ZCPy2rUivS=yAWAKYyJ9+mg@mail.gmail.com>
Le mer. 11 déc. 2024 à 15:00, jian he <[email protected]> a
écrit :
> do we also need to update doc/src/sgml/rules.sgml?
>
> https://www.postgresql.org/docs/current/rules-materializedviews.html
>
Yes, you're right. Here is a fix. (Not a patch with a commit message as I
don't know if David will want to commit everything (the CI issue, this, and
probably more) in one patch.)
--
Guillaume.
Attachments:
[text/x-patch] rules.patch (2.0K, ../CAECtzeXsJbONQDGu1dC9f_RO2WLFK2ZwPNrFKTvYMbWE+w-oAQ@mail.gmail.com/3-rules.patch)
download | inline diff:
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7a928bd7b9..6cebed5c37 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1042,12 +1042,16 @@ SELECT count(*) FROM words WHERE word = 'caterpiler';
If the materialized view is used instead, the query is much faster:
<programlisting>
- Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
- -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
+ Aggregate (cost=4.44..4.45 rows=1 width=8) (actual time=0.138..0.139 rows=1 loops=1)
+ Buffers: shared read=3
+ -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.132..0.133 rows=0 loops=1)
Index Cond: (word = 'caterpiler'::text)
Heap Fetches: 0
- Planning time: 0.164 ms
- Execution time: 0.117 ms
+ Buffers: shared read=3
+ Planning:
+ Buffers: shared hit=5
+ Planning Time: 0.143 ms
+ Execution Time: 0.200 ms
</programlisting>
Either way, the word is spelled wrong, so let's look for what we might
@@ -1087,11 +1091,15 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
Using the materialized view:
<programlisting>
- Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
- -> Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
+ Limit (cost=0.29..1.06 rows=10 width=14) (actual time=135.027..135.475 rows=10 loops=1)
+ Buffers: shared hit=4465
+ -> Index Scan using wrd_trgm on wrd (cost=0.29..37224.81 rows=479826 width=14) (actual time=135.023..135.470 rows=10 loops=1)
Order By: (word <-> 'caterpiler'::text)
- Planning time: 0.196 ms
- Execution time: 198.640 ms
+ Buffers: shared hit=4465
+ Planning:
+ Buffers: shared hit=80
+ Planning Time: 0.767 ms
+ Execution Time: 140.836 ms
</programlisting>
If you can tolerate periodic update of the remote data to the local
view thread (32+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
In-Reply-To: <CAECtzeXsJbONQDGu1dC9f_RO2WLFK2ZwPNrFKTvYMbWE+w-oAQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox