Re: [HACKERS] psql nested queries with 2000+ records

Coronach(at)hill-b-073(dot)resnet(dot)purdue(dot)edu blushes and says:
> Aparently, the postgresql server was moved from the BSDI box to a linux
> 2.0.30 box, so the numbers that where given where not from the bsdi box. So
> now I am doing the queries personally. I'm sorry for the run around.
>
> This is the responce from the explain on the BSDI 3.1 box.
>
> amusements=> explain select name from games where name in (select name from
> game
> s where name like 'A%');
> NOTICE: QUERY PLAN:
>
> Seq Scan on games (cost=0.00 size=0 width=12)
> SubPlan
> -> Seq Scan on games (cost=0.00 size=0 width=12)
>
> EXPLAIN
>
> amusements=> explain select name from games where name in (select name from
> game
> s2 where mfr = '');
> NOTICE: QUERY PLAN:
>
> Seq Scan on games (cost=0.00 size=0 width=12)
> SubPlan
> -> Seq Scan on games2 (cost=0.00 size=0 width=12)
>
> EXPLAIN
>
> amusements=> explain select * from games where mfr = '';
> NOTICE: QUERY PLAN:
>
> Seq Scan on games (cost=0.00 size=0 width=160)
>
> EXPLAIN
>
> Once again, I appologize for this misinformation and hope this sheds some
> light to the original problem. I'm making a note to change permissions on
> who can do what from now on.
>
> On a side note, the query was done on the 2.0.30 box in 5 minutes compared
> to the BSDI box at 15+.

Well of course... ;-)

I am sure this has been mentioned, but could you post the exact schema for
these tables and their indexes? It might even be worth dropping and
recreating the indexes just to be _sure_.

-dg

David Gould dg(at)illustra(dot)com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- I realize now that irony has no place in business communications.