Re: [GENERAL] ExecInitIndexScan ERROR?

What PostgreSQL version are you running?

Hi,

I just got the following error message and have no clue what I'm doing wrong:

ERROR: ExecInitIndexScan: both left and right op's are rel-vars

Everything works fine with the following query:

explain select p0.protein
from dom_prot p0, dom_prot p1, dom_prot p2, dom_prot p3
where p0.protein = p1.protein
and p1.protein = p2.protein
and p0.start < p1.start
and p1.start < p2.start
and p0.domain = 'SH3'
and p1.domain = 'SH2'
and p2.domain = 'SH3';

NOTICE: QUERY PLAN:

Nested Loop (cost=6.05 size=1 width=48)
-> Nested Loop (cost=4.05 size=1 width=32)
-> Index Scan on p2 (cost=2.05 size=1 width=16)
-> Index Scan on p1 (cost=2.00 size=1 width=16)
-> Index Scan on p0 (cost=2.00 size=1 width=16)

EXPLAIN

Adding one to the 'from' list leads to the error message. Here' the query and
it's plan:

explain select p0.protein
from dom_prot p0,
dom_prot p1,
dom_prot p2,
dom_prot p3 -- the new guy
where p0.protein = p1.protein
and p1.protein = p2.protein
and p0.start < p1.start
and p1.start < p2.start
and p0.domain = 'SH3'
and p1.domain = 'SH2'
and p2.domain = 'SH3';

NOTICE: QUERY PLAN:

Nested Loop (cost=3088.11 size=64517 width=52)
-> Nested Loop (cost=6.05 size=1 width=48)
-> Nested Loop (cost=4.05 size=1 width=32)
-> Index Scan on p2 (cost=2.05 size=1 width=16)
-> Index Scan on p1 (cost=2.00 size=1 width=16)
-> Index Scan on p0 (cost=2.00 size=1 width=16)
-> Seq Scan on p3 (cost=3082.06 size=64517 width=4)

EXPLAIN

As it might have something to do with my indices (??), here's what they look
like:

CREATE INDEX dp_domain_ind
ON dom_prot USING btree(domain text_ops);
CREATE INDEX dp_score_ind
ON dom_prot USING btree(score int4_ops);

And the table itself is:

CREATE TABLE dom_prot
(
domain text not null,
protein text not null,
start int4 not null,
ende int4 not null,
score int4,
e_value float8,
p_value char16,
primary key (domain, protein, start, ende)
);

Sorry for that quite extensive mail,

Joerg

--
Internet Joerg.Schultz@embl-heidelberg.de
Address EMBL, Meyerhofstr 1, 69012 Heidelberg, Germany
Tel +49 (0)6221 387 534, Fax +49 (0)6221 387517
URL http://www.embl-heidelberg.de/~jschultz/

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)