Received: from dcave.digsys.bg (root@dcave.digsys.bg [192.92.129.5])
	by hub.org (8.9.3/8.9.3) with ESMTP id EAA71397
	for <pgsql-hackers@hub.org>; Wed, 9 Jun 1999 04:04:35 -0400 (EDT)
	(envelope-from daniel@dcave.digsys.bg)
Received: from dcave.digsys.bg (daniel@localhost.digsys.bg [127.0.0.1])
	by dcave.digsys.bg (8.9.0/8.9.0) with ESMTP id LAA09263
	for <pgsql-hackers@hub.org>; Wed, 9 Jun 1999 11:04:29 +0300 (EEST)
Message-Id: <199906090804.LAA09263@dcave.digsys.bg>
From: Daniel Kalchev <daniel@digsys.bg>
To: pgsql-hackers@hub.org
Subject: Postgres 6.5 beta2 and beta3 problem
Content-Transfer-Encoding: quoted-printable
Date: Wed, 09 Jun 1999 11:04:28 +0300
Sender: daniel@digsys.bg

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 Pos=
tgres =

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=3D3335.38 rows=3D1 width=3D1=
2)

and runs for several minutes. If I drop the w_k_words_i index, the explai=
n is:

Seq Scan on words  (cost=3D7609.52 rows=3D1 width=3D12)

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 ,arc=
hived =

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=3Db.serviceid =

 	AND c.serviceid=3Da.serviceid
GROUP BY custid, serviceid, subserviceid;

works, although runs for indefinitely long time (due to the subselect - b=
ut =

this is not a problem, as it can be rewritten). Under Postgres 6.5 hwoeve=
r, it =

is not accepted, because there are no aggregates in the target list. Is t=
his =

incorrect behavior of the 6.4.2 version or 6.5 has different syntax?

Regards,
Daniel Kalchev