Re: Performance problem with 50,000,000 rows

David Link wrote:

Just, reading it and I always enter these kind of queries like:

select i.isbn,
t.vendor,
i.store,
i.qty
from bk_inv i,
bk_title t
where
t.vendor = '01672708' and
i.isbn = t.isbn;

Don't know if it makes a difference but I can imagine that it could because
the t.vendor = '...' limits the possibilities in the first stage.

Regards,

Feite

> I'm new to PG but this just seems wrong. Can someone take a look:
>
> .-----------. .-----------.
> | bk_inv | | bk_title |
> |-----------| |-----------|
> | isbn |<--->| isbn |
> | store | | vendor |
> | qty | | |
> | week | `-----------'
> | | 2,000,000 recs
> `-----------'
> 50,000,000 recs
>
> Actual record numbers:
> bk_inv : 46,790,877
> bk_title: 2,311,710
>
> VENDOR REPORT
>
> A list of Inventory items, for any one given vendor (e.q. 01672708)
>
> select i.isbn,
> t.vendor,
> i.store,
> i.qty
> from bk_inv i,
> bk_title t
> where i.isbn = t.isbn
> and t.vendor = '01672708' ;
>
> This query should be instantaneous. Granted that's 50 million rows, but
> I have created an index on the isbn column for both tables.
> After about 25 minutes (on 4 processor Del 6300 with 1GB Memory) it
> spits out:
>
> ERROR: Write to hashjoin temp file failed
>
> tiger=# explain select * from bk_inv i, bk_title t where i.isbn = t.isbn
> and t.vendor ='50000029';
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=0.00..11229637.06 rows=2172466 width=72)
> -> Index Scan using bk_title_isbn_idx on bk_title t
> (cost=0.00..390788.08 rows=107331 width=24)
> -> Index Scan using bk_inv_isbn_idx on bk_inv i
> (cost=0.00..10252621.38 rows=46790877 width=48)
>
> BIG COST!
>
> These explain queries show the existance of the indexes and give small
> costs:
>
> tiger=# explain select * from bk_title where isbn = '50000029';
> NOTICE: QUERY PLAN:
>
> Index Scan using bk_title_isbn_idx on bk_title (cost=0.00..4.90 rows=1
> width=24)
>
> tiger=# explain select * from bk_inv where isbn = '0897474228';
> NOTICE: QUERY PLAN:
>
> Index Scan using bk_inv_isbn_idx on bk_inv (cost=0.00..225.53 rows=55
> width=48)
>
> Note. Same tables, same query returns instantaneously with Oracle 8.1.
> What I am hoping to show is that Postgres can do our job too.
>
> Any help on this much obliged. (Yes I ran vacuum analyze).
>
> David Link
> White Plains, NY
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Feite Brekeveld
feite(dot)brekeveld(at)osiris-it(dot)nl
http://www.osiris-it.nl