From: Daniel Kalchev <daniel@digsys.bg>
To: pgsql-hackers@hub.org
Subject: Postgres 6.5 beta2 and beta3 problem
Date: Wed, 09 Jun 1999 11:04:28 +0300
Message-ID: <199906090804.LAA09263@dcave.digsys.bg> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: daniel@digsys.bg, pgsql-hackers@hub.org
Subject: Re: Postgres 6.5 beta2 and beta3 problem
In-Reply-To: <199906090804.LAA09263@dcave.digsys.bg>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox