[GENERAL] query optimizer questions
Why do the following three queries use different scan types under 7.1.2? Under 6.5.3 they all used index (and my system ran MUCH faster, since I often use the trailing %) I've done a vacuum analyze. I installed 7.1.2 on RedHat 6.2 using the RPMs. anderson=# explain select value from s_n_grantor where value='foobar'; NOTICE: QUERY PLAN: Index Scan using s_n_grantor_key on s_n_grantor (cost=0.00..823.58 rows=247 width=12) EXPLAIN anderson=# explain select value from s_n_grantor where value like 'foobar'; NOTICE: QUERY PLAN: Seq Scan on s_n_grantor (cost=0.00..8178.46 rows=247 width=12) anderson=# explain select value from s_n_grantor where value like 'foobar%'; NOTICE: QUERY PLAN: Seq Scan on s_n_grantor (cost=0.00..8178.46 rows=1 width=12) EXPLAIN anderson=# \d s_n_grantor_key Index "s_n_grantor_key" Attribute | Type ---+--- value | character varying(80) btree anderson=# \d s_n_grantor Table "s_n_grantor" Attribute | Type | Modifier ---+---+-- doc | character varying(16) | not null entry | smallint | not null value | character varying(80) | Indices: s_n_grantor_key, s_n_grantor_pkey ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] query optimizer questions
Thanks. How do I set the locale? > In 7.1, LIKE optimization (i.e., possibly using an index) has been > disabled unless you are in the C locale because it does not work > correctly. You could set your locale to C and re-initdb to get it to work > again. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] query optimizer questions
Robert Berger <[EMAIL PROTECTED]> writes: > Why do the following three queries use different scan types under 7.1.2? As Peter points out, index optimization of LIKE queries is disabled unless your database is in C locale (if you're not sure, contrib/pg_controldata can tell you what locale your database is using). However, the estimated row counts seem peculiar to me in any case: > anderson=# explain select value from s_n_grantor where value='foobar'; > NOTICE: QUERY PLAN: > Index Scan using s_n_grantor_key on s_n_grantor (cost=0.00..823.58 > rows=247 width=12) > EXPLAIN > anderson=# explain select value from s_n_grantor where value like > 'foobar'; > NOTICE: QUERY PLAN: > Seq Scan on s_n_grantor (cost=0.00..8178.46 rows=247 width=12) > anderson=# explain select value from s_n_grantor where value like > 'foobar%'; > NOTICE: QUERY PLAN: > Seq Scan on s_n_grantor (cost=0.00..8178.46 rows=1 width=12) I'd expect the LIKE 'foobar' to produce the same estimate as = 'foobar', because the LIKE estimator recognizes this as a fixed pattern. But why is that considered *less* selective than LIKE 'foobar%'? Something fishy about that. Could we see the output of select attname,attdispersion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 's_n_grantor'; (If you get no output, it means you didn't VACUUM ANALYZE ...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] substring wrong example in manual
RE: http://www.postgresql.org/idocs/index.php?functions-string.html
In Table 4-6. SQL String Functions and Operators
FUNCTIONRETURN TYPE DESC
...
substring( text extract
string substring
[from integer]
[for integer])
EXAMPLE RESULT
substring('Thomas' oma
from 2 for 3)
...
The result should be 'hom' instead of 'oma':
1 2 3 4 5 6
T h o m a s
from 1 2
^---^
for1 2 3
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
