Re: [HACKERS] subselect and optimizer

I will say we have an optimization problem with tables being referenced
multiple times in a query, but I don't know if this is the cause, though
you could test it by making a copy of order_tbl with another name, and
testing the speed.
>
> Hi,
>
> This question was sent to me by a user who uses PostgreSQL 6.3.1.
> Is this normal?
> (Note that the patch for src/backend/optimizer/path/prune.c created by
> Vadim did not help)
> --
> Tatsuo Ishii
> t-ishii(at)sra(dot)co(dot)jp
> ------------------------------------------------------------------
> The following query seems to generate a rather slow query plan.
>
> explain select * from product,order_tbl where
> product.serial=order_tbl.serial and product.serial in (select serial
> from order_tbl where cust_id='ABCDE');
>
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=906.09 size=744 width=110)
> -> Seq Scan on order_tbl (cost=296.13 size=6822 width=36)
> -> Hash (cost=0.00 size=0 width=0)
> -> Seq Scan on product (cost=358.29 size=744 width=74)
> SubPlan
> -> Index Scan on order_tbl (cost=2.05 size=1 width=12)
>
> EXPLAIN
>
> product and order_tbl are defined as follows:
>
> create table product (
> serial char(10) primary key,
> pname char(15) not null,
> price int2);
> create index prod_name on product using hash(pname);
>
> create table order_tbl (
> cust_id char(5) primary key,
> serial char(10) not null,
> nums int2,
> o_date date);
> create index order_ser on order_tbl using hash(serial);
>
> * product has 7289 tuples, and order_tbl has 6818 tuples.
>
>

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)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)