Hi,
I've experienced a brutal speedup (order of 2) separateing the following
subquery and making it manually:
explain select name,description
from descriptions
where in (select name from descriptions where description like '%Bankverbindung%');
Seq Scan on descriptions (cost=163.98 rows=3575 width=24) SubPlan -> Seq Scan on descriptions (cost=163.98 rows=2
width=12)
[I had no patient to wait the resuls...]
If I give manually the subquery it gives an immediate response (5 records
at all). If I copy manually that 5 record as constants after 'in', then I
receive an immediate response from the main query too!
explain select name,description from descriptions where name in
('accounts', 'no_account', 'no_account', 'no_acc', 'account');
Index Scan using desc_ind3, desc_ind3, desc_ind3, desc_ind3 on
descriptions (cost=9.01 rows=36 width=24)
I saw in the archive that in, and not in can cause performance
degradation, but this is not the case now. booth queries are using 'in'
Why can't be the subquery evaluated first, and then run the main query
with the results?
============== the stucture: ===============================
CREATE TABLE "descriptions" ( "objectid" int4 DEFAULT nextval ( 'DescSeq' ) NOT NULL, "ts" int4 NOT NULL,
"classid" int4 NOT NULL, "languageid" int4 NOT NULL, "priority" int4 NOT NULL, "name" character
varying(50)NOT NULL, "description" character varying(200));
CREATE INDEX "desc_ind3" on "descriptions" using btree ( "name"
"varchar_ops" );
CREATE INDEX "desc_ind2" on "descriptions" using btree ( "description"
"varchar_ops" );
CREATE INDEX "desc_ind1" on "descriptions" using btree ( "classid"
"int4_ops", "name" "varchar_ops" );
CREATE UNIQUE INDEX "desc_idx" on "descriptions" using btree ( "classid"
"int4_ops", "languageid" "int4_ops", "name" "varchar_ops" );
CREATE UNIQUE INDEX "descriptions_pkey" on "descriptions" using btree (
"objectid" "int4_ops" );
version: postgresql 6.5, debian potato.
