From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Berger <rwb(at)vtiscan(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: query optimizer questions |
Date: | 2001-07-02 19:26:54 |
Message-ID: | 11682.994102014@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Robert Berger <rwb(at)vtiscan(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Clinton James | 2001-07-02 20:30:21 | pqReadData() -- backend closed on COPY |
Previous Message | Clinton James | 2001-07-02 19:23:32 | RE: pqReadData() -- backend closed on COPY |