From c1e2f351f8c27f8f3b9c8deb2d05eb10fec18c9c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 25 Jun 2002 03:32:31 +0000 Subject: [PATCH] Add more info on regex's using INDEX. --- doc/FAQ | 20 +++++++++++++++----- doc/src/FAQ/FAQ.html | 37 +++++++++++++++++++++++++++++-------- 2 files changed, 44 insertions(+), 13 deletions(-) diff --git a/doc/FAQ b/doc/FAQ index 0cc05b3d3b..9c2f01a3e5 100644 --- a/doc/FAQ +++ b/doc/FAQ @@ -1,7 +1,7 @@ Frequently Asked Questions (FAQ) for PostgreSQL - Last updated: Mon Jun 24 21:45:50 EDT 2002 + Last updated: Mon Jun 24 23:32:16 EDT 2002 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) @@ -747,10 +747,20 @@ LIMIT 1 When using wild-card operators such as LIKE or ~, indexes can only be - used if the default C local is used during initdb and the beginning of - the search is anchored to the start of the string. Therefore, to use - indexes, LIKE patterns must not start with %, and ~(regular - expression) patterns must start with ^. + used in certain circumstances: + * The beginning of the search string must be anchored to the start + of the string, i.e.: + + * LIKE patterns must not start with %. + * ~(regular expression) patterns must start with ^. + + The search string can not start with a character class, e.g. [a-e]. + + Case-insensitive searches like ILIKE and ~* can not be used. + Instead, use functional indexes, which are described later in this + FAQ. + + The default C local must have been used during initdb. 4.9) How do I see how the query optimizer is evaluating my query? diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index 3240a40685..ab108d984d 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -14,7 +14,7 @@ alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

-

Last updated: Mon Jun 24 21:45:50 EDT 2002

+

Last updated: Mon Jun 24 23:32:16 EDT 2002

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
@@ -282,6 +282,7 @@ subscribe end + Digests are sent out to members of this list whenever the main list has received around 30k of messages. @@ -293,6 +294,7 @@ subscribe end + There is also a developers discussion mailing list available. To subscribe to this list, send email to pgsql-hackers-request@PostgreSQL.org @@ -860,6 +862,7 @@ Maximum number of columns in a table? 250-1600 depending on column types Maximum number of indexes on a table? unlimited + Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer when these values get unusually large. @@ -951,11 +954,24 @@

When using wild-card operators such as LIKE or - ~, indexes can only be used if the default C local is used -during initdb and the beginning of the search - is anchored to the start of the string. Therefore, to use indexes, - LIKE patterns must not start with %, and - ~(regular expression) patterns must start with ^.

+ ~, indexes can only be used in certain circumstances: + +

4.9) How do I see how the query optimizer is evaluating my query?

@@ -1010,13 +1026,12 @@ during initdb and the beginning of the search SELECT * FROM tab WHERE lower(col) = 'abc' - + This will not use an standard index. However, if you create a functional index, it will be used: 
     CREATE INDEX tabindex on tab (lower(col));
-

4.13) In a query, how do I detect if a field @@ -1066,6 +1081,7 @@ BYTEA bytea variable-length byte array (null-byte safe) name TEXT ); + is automatically translated into this: 
     CREATE SEQUENCE person_id_seq;
@@ -1075,6 +1091,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     );
     CREATE UNIQUE INDEX person_id_key ON person ( id );
+ See the create_sequence manual page for more information about sequences. You can also use each row's OID field as a unique value. However, if you need to dump and reload the database, @@ -1093,6 +1110,7 @@ BYTEA bytea variable-length byte array (null-byte safe) new_id = output of "SELECT nextval('person_id_seq')" INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal'); + You would then also have the new value stored in new_id for use in other queries (e.g., as a foreign key to the person table). Note that the name of the @@ -1108,6 +1126,7 @@ BYTEA bytea variable-length byte array (null-byte safe) INSERT INTO person (name) VALUES ('Blaise Pascal'); new_id = output of "SELECT currval('person_id_seq')"; + Finally, you could use the OID returned from the INSERT statement to look up the default value, though this is probably the least portable approach. @@ -1215,6 +1234,7 @@ BYTEA bytea variable-length byte array (null-byte safe) ulimit -d 262144 limit datasize 256m + Depending on your shell, only one of these may succeed, but it will set your process data segment limit much higher and perhaps allow the query to complete. This command applies to the current process, @@ -1273,6 +1293,7 @@ BYTEA bytea variable-length byte array (null-byte safe) WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) + We hope to fix this limitation in a future release.

4.23) How do I perform an outer join?

-- 2.40.0