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