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@sra.co.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@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)