Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbfabd4f

Browse files
committed
Add scripts to enable/disable use of new LIKE/regexp selectivity
estimation operators. See contrib/likeplanning/README for info.
1 parentea46ddc commitbfabd4f

File tree

5 files changed

+164
-0
lines changed

5 files changed

+164
-0
lines changed

‎contrib/README

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,12 @@ isbn_issn -
3535
PostgreSQL type extensions for ISBN (books) and ISSN (serials)
3636
by Garrett A. Wollman <wollman@khavrinen.lcs.mit.edu>
3737

38+
likeplanning -
39+
Scripts to enable/disable new planning code for LIKE and regexp
40+
pattern match operators. These will go away again once the code
41+
is mature enough to enable by default.
42+
by Tom Lane <tgl@sss.pgh.pa.us>
43+
3844
linux -
3945
Start postgres back end system
4046
by Thomas Lockhart <lockhart@alumni.caltech.edu>

‎contrib/likeplanning/README

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
This directory contains three SQL scripts that control use of some new
2+
code for planning/optimizing queries containing LIKE and
3+
regular-expression operators. This code was added to Postgres 7.0 late in
4+
beta test, and it hasn't gotten enough testing to warrant turning it on by
5+
default in release 7.0 (although it probably will become default in 7.1).
6+
So, here are some scripts to enable and disable it. You may want to run
7+
these scripts if you have problems with the planner choosing bad plans for
8+
queries involving LIKE or regexps in WHERE clauses.
9+
10+
11+
HOW TO USE THE SCRIPTS
12+
----------------------
13+
14+
All three scripts must be run as the Postgres superuser. The easiest
15+
way to run an SQL script is
16+
psql -f scriptfile databasename
17+
or you can start psql interactively and enter
18+
\i scriptfile
19+
20+
enablelike.sql enables use of the new planning code in the database in
21+
which it is run. If you run it in template1, all subsequently-created
22+
databases will use the new code by default.
23+
24+
disablelike.sql reverts to the old planning code for LIKE, in the database
25+
in which it is run. If you run it in template1, all subsequently-created
26+
databases will use the old code by default.
27+
28+
If your database was initdb'd with release 7.0beta5, you need to run
29+
updatepgproc.sql before you can run enablelike.sql. Databases initdb'd
30+
with 7.0RC1 or later already have pg_proc entries for the new code, so
31+
updatepgproc.sql is unnecessary for them. If enablelike.sql produces
32+
errors like "No procedure with name regexeqsel", then you need to run
33+
updatepgproc.sql.
34+
35+
36+
WHAT IT DOES
37+
------------
38+
39+
These scripts install (or disable) new code for "selectivity estimation"
40+
of LIKE and regexp operators. Selectivity estimation determines the
41+
estimated number of rows produced by a query or subquery, and that in turn
42+
determines the kind of plan the planner will use. The old selectivity
43+
estimator ignored the pattern being searched for and just produced the
44+
same estimate as for an "=" operator, which of course was usually too low
45+
for a wildcard match. The new code has some knowledge of pattern matching
46+
rules and generates an estimate based on the number of fixed characters and
47+
wildcards present in the pattern. Also, if the pattern has a fixed prefix
48+
that must be matched (such as LIKE 'foo%' or ~ '^foo'), an appropriate
49+
range-query selectivity estimate is produced and factored into the result.
50+
51+
If you want to look at the code itself, see src/backend/utils/adt/selfuncs.c.

‎contrib/likeplanning/disablelike.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
-- This script disables use of the new LIKE-related selectivity estimation
2+
-- functions, which are a little too new to be enabled by default in 7.0.
3+
-- You can enable them again by running enablelike.sql.
4+
5+
-- Use of the functions will be disabled only in those databases you
6+
-- run this script in. If you run it in template1,
7+
-- all subsequently-created databases will not use the functions.
8+
9+
-- Be sure to run the script as the Postgres superuser!
10+
11+
UPDATE pg_operatorSET
12+
oprrest='eqsel'::regproc,
13+
oprjoin='eqjoinsel'::regproc
14+
WHERE oprrest='regexeqsel'::regproc;
15+
16+
UPDATE pg_operatorSET
17+
oprrest='eqsel'::regproc,
18+
oprjoin='eqjoinsel'::regproc
19+
WHERE oprrest='icregexeqsel'::regproc;
20+
21+
UPDATE pg_operatorSET
22+
oprrest='eqsel'::regproc,
23+
oprjoin='eqjoinsel'::regproc
24+
WHERE oprrest='likesel'::regproc;
25+
26+
UPDATE pg_operatorSET
27+
oprrest='neqsel'::regproc,
28+
oprjoin='neqjoinsel'::regproc
29+
WHERE oprrest='regexnesel'::regproc;
30+
31+
UPDATE pg_operatorSET
32+
oprrest='neqsel'::regproc,
33+
oprjoin='neqjoinsel'::regproc
34+
WHERE oprrest='icregexnesel'::regproc;
35+
36+
UPDATE pg_operatorSET
37+
oprrest='neqsel'::regproc,
38+
oprjoin='neqjoinsel'::regproc
39+
WHERE oprrest='nlikesel'::regproc;

‎contrib/likeplanning/enablelike.sql

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
-- This script enables use of the new LIKE-related selectivity estimation
2+
-- functions, which are a little too new to be enabled by default in 7.0.
3+
-- You can disable them again by running disablelike.sql.
4+
5+
-- If your database was initdb'd with 7.0beta5, you need to run
6+
-- updatepgproc.sql first. You can tell that is necessary if this
7+
-- script produces errors like "No procedure with name regexeqsel".
8+
9+
-- Use of the functions will be enabled only in those databases you
10+
-- run this script in. If you run it in template1,
11+
-- all subsequently-created databases will use the functions.
12+
13+
-- Be sure to run the script as the Postgres superuser!
14+
15+
UPDATE pg_operatorSET
16+
oprrest='regexeqsel'::regproc,
17+
oprjoin='regexeqjoinsel'::regproc
18+
WHERE oprrest='eqsel'::regprocAND oprname='~';
19+
20+
UPDATE pg_operatorSET
21+
oprrest='icregexeqsel'::regproc,
22+
oprjoin='icregexeqjoinsel'::regproc
23+
WHERE oprrest='eqsel'::regprocAND oprname='~*';
24+
25+
UPDATE pg_operatorSET
26+
oprrest='likesel'::regproc,
27+
oprjoin='likejoinsel'::regproc
28+
WHERE oprrest='eqsel'::regprocAND oprname='~~';
29+
30+
UPDATE pg_operatorSET
31+
oprrest='regexnesel'::regproc,
32+
oprjoin='regexnejoinsel'::regproc
33+
WHERE oprrest='neqsel'::regprocAND oprname='!~';
34+
35+
UPDATE pg_operatorSET
36+
oprrest='icregexnesel'::regproc,
37+
oprjoin='icregexnejoinsel'::regproc
38+
WHERE oprrest='neqsel'::regprocAND oprname='!~*';
39+
40+
UPDATE pg_operatorSET
41+
oprrest='nlikesel'::regproc,
42+
oprjoin='nlikejoinsel'::regproc
43+
WHERE oprrest='neqsel'::regprocAND oprname='!~~';

‎contrib/likeplanning/updatepgproc.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
-- This script loads pg_proc entries for the 7.0 selectivity estimation
2+
-- functions into a 7.0beta5 database. You should not run it if you
3+
-- initdb'd with 7.0RC1 or later. If you do need it, run it in each
4+
-- database you have, including template1. Once you have run it in
5+
-- template1, all subsequently-created databases will contain the entries,
6+
-- so you won't need to run it again.
7+
-- Be sure to run the script as the Postgres superuser!
8+
9+
COPY pg_proc WITH OIDSFROM stdin;
10+
1818regexeqsel011ftf5f70126262102310000100regexeqsel-
11+
1819likesel011ftf5f70126262102310000100likesel-
12+
1820icregexeqsel011ftf5f70126262102310000100icregexeqsel-
13+
1821regexnesel011ftf5f70126262102310000100regexnesel-
14+
1822nlikesel011ftf5f70126262102310000100nlikesel-
15+
1823icregexnesel011ftf5f70126262102310000100icregexnesel-
16+
1824regexeqjoinsel011ftf5f701262621262110000100regexeqjoinsel-
17+
1825likejoinsel011ftf5f701262621262110000100likejoinsel-
18+
1826icregexeqjoinsel011ftf5f701262621262110000100icregexeqjoinsel-
19+
1827regexnejoinsel011ftf5f701262621262110000100regexnejoinsel-
20+
1828nlikejoinsel011ftf5f701262621262110000100nlikejoinsel-
21+
1829icregexnejoinsel011ftf5f701262621262110000100icregexnejoinsel-
22+
\.
23+
24+
UPDATE pg_procSET proowner=pg_shadow.usesysid
25+
WHEREoid>=1818ANDoid<=1829ANDpg_shadow.usename=CURRENT_USER;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp