Обсуждение: Postgres 6.5 beta2 and beta3 problem

Hello,

Sorry that I picked this too late in the release cycle, but other work
prevented my earlier involvement in the testing.

There are at least two serious problems that I discovered so far with Postgres
6.5 beta2 and beta3 (running on BSD/OS 4.0.1):

1. LIKE with indexes works worse than without indexes.

Given the following schema:

CREATE TABLE "words" (       "w_key" text,       "w_pages" text);
CREATE  INDEX "w_k_words_i" on "words" using btree ( "w_key" "text_ops" );

The table words has 117743 records.

the folowing query:

select w_key from words where w_key like 'sometext%'

is explained as:

Index Scan using w_k_words_i on words  (cost=3335.38 rows=1 width=12)

and runs for several minutes. If I drop the w_k_words_i index, the explain is:

Seq Scan on words  (cost=7609.52 rows=1 width=12)

and the query runs noticeably faster.

Under 6.4 the behavior is as expected, much better with indexes.

2. Under Postgres 6.4 the following query:

SELECT config.serviceid, custid, datetime_date( updated_at ) as date ,archived
as a, c.subserviceid as ss, c.usage_price as    price, c.usage_included as time, service
FROM  a, b, c
WHERE confid in ( SELECT confid            FROM a           WHERE archived_at > '30-04-1999'              AND
created_at< '30-04-1999' )    AND not archived        AND a.serviceid=b.serviceid     AND c.serviceid=a.serviceid 
GROUP BY custid, serviceid, subserviceid;

works, although runs for indefinitely long time (due to the subselect - but
this is not a problem, as it can be rewritten). Under Postgres 6.5 hwoever, it
is not accepted, because there are no aggregates in the target list. Is this
incorrect behavior of the 6.4.2 version or 6.5 has different syntax?

Regards,
Daniel Kalchev

Daniel Kalchev <daniel@digsys.bg> writes:
> 1. LIKE with indexes works worse than without indexes.

Since you are using USE_LOCALE, the parser is inserting only a one-sided
index restriction; that isWHERE w_key like 'sometext%'
becomesWHERE w_key like 'sometext%' AND w_key >= 'sometext'
whereas without USE_LOCALE it becomesWHERE w_key like 'sometext%' AND w_key >= 'sometext'    AND w_key <=
'sometext\377'
6.4 always did the latter, which was wrong in non-ASCII locales because
\377 might not be the highest character in the sort order.  (Strictly
speaking it's wrong in ASCII locale as well...)

Of course, the one-sided index restriction is much less selective than
the two-sided; depending on what 'sometext' actually is, you might end
up scanning most of the table, and since index scan is much slower
per-tuple-scanned than sequential scan, you lose.  That's evidently
what's happening here.

I suspect that the optimizer's cost estimates need refinement;
it should be able to guess that the sequential scan will be the
faster choice here.

Of course what you really want is a two-sided index restriction,
but we are not going to be able to fix that until someone figures
out a locale-independent way of generating a "slightly larger"
comparison string.  So far I have not heard any proposals that
sound like they will work...

> Under Postgres 6.5 hwoever, it
> is not accepted, because there are no aggregates in the target list.

No, that's not what it's unhappy about; it's unhappy because there
are ungrouped fields used in the target list.  This is erroneous
SQL because there's no unique choice of value to return for such an
field (if several tuples are grouped together, which one's value
of the field do you use?)  Prior versions of Postgres failed to detect
this error, but it's an error.  You were getting randomly selected
values for the ungrouped fields, I suppose.
        regards, tom lane