Обсуждение: Problem with nested query

Hello,

I have quite strange problem with nested query.

mails=> explain INSERT INTO member(email)
mails->         select distinct email
mails->                 FROM iaddress
mails->                 WHERE NOT EXISTS (
mails(>                         SELECT email
mails(>                                 FROM  member
mails(>                                 WHERE member.email=iaddress.email);
NOTICE:  QUERY PLAN:

Unique  (cost=1869591274.28..1869591274.28 rows=0 width=12)
  ->  Sort  (cost=1869591274.28..1869591274.28 rows=1 width=12)
        ->  Seq Scan on iaddress  (cost=0.00..1869591274.27 rows=1 width=12)
              SubPlan
                ->  Seq Scan on member  (cost=0.00..10226.27 rows=1 width=12)

EXPLAIN

As far as you can see it uses sequence scan for inner select. This is quite
strange as if I do this select with a constant it will use index scan:

mails=> explain SELECT email
mails->                                 FROM  member
mails->                                 WHERE member.email='test';
NOTICE:  QUERY PLAN:

Index Scan using ix_member_email on member  (cost=0.00..4.76 rows=1 width=12)

EXPLAIN

Additional data.

mails=> select count(*) from iaddress;
 count
-------
 58742
(1 row)

mails=> select count(*) from member;
 count
--------
 219648
(1 row)

There is an index member(email).
PostgreSQL 7.0.2.

Any ideas?

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Denis Perchine <dyp@perchine.com> writes:
> As far as you can see it uses sequence scan for inner select. This is quite
> strange as if I do this select with a constant it will use index scan:

Are the two tables' "email" fields declared as exactly the same
datatype?

IIRC, 7.0.* has a problem with recognizing that a type-coerced parameter
to an inner query is useful as an indexscan reference constant.  This is
fixed in current sources, but in the meantime avoiding an implicit type
coercion is the easiest workaround.

            regards, tom lane

> Denis Perchine <dyp@perchine.com> writes:
> > As far as you can see it uses sequence scan for inner select. This is
> > quite strange as if I do this select with a constant it will use index
> > scan:
>
> Are the two tables' "email" fields declared as exactly the same
> datatype?

No...

> IIRC, 7.0.* has a problem with recognizing that a type-coerced parameter
> to an inner query is useful as an indexscan reference constant.  This is
> fixed in current sources, but in the meantime avoiding an implicit type
> coercion is the easiest workaround.

OK. Thanks.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------