Re: query produces 1 GB temp file

Dirk Lutzebaeck wrote:

>Hi,
>
>here is a query which produces over 1G temp file in pgsql_tmp. This
>is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB
>sort_mem and 320MB shared_mem.
>
>Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All
>tables have been analyzed before.
>
>Can some please explain why the temp file is so huge? I understand
>there are a lot of rows.
>
>Thanks in advance,
>
>Dirk
>  
>
...

>               ->  Nested Loop  (cost=0.00..8346.73 rows=3 width=1361) (actual time=34.104..18016.005 rows=703677 loops=1)
>  
>
Well, there is this particular query where it thinks there will only be 
3 rows, but in fact there are 703,677 of them. And the previous line:

>         ->  Sort  (cost=8346.75..8346.76 rows=3 width=1361) (actual time=75357.448..75499.263 rows=22439 loops=1)
>  
>
Seem to indicate that after sorting you still have 22,439 rows, which 
then gets pared down again down to 1000.

I'm assuming that the sort you are trying to do is extremely expensive. 
You are sorting 700k rows, which takes up too much memory (1GB), which 
forces it to create a temporary table, and write it out to disk.

I didn't analyze it a lot, but you might get a lot better performance 
from doing a subselect, rather than the query you wrote.

You are joining 4 tables (bi, en, df AS ft, es) I don't know which 
tables are what size. In the end, though, you don't really care about 
the en table or es tables (they aren't in your output).

So maybe one of you subselects could be:

where bi.en = (select en from es where es.co = bi.co and es.spec=122293729);

I'm pretty sure the reason you need 1GB of temp space is because at one 
point you have 700k rows. Is it possible to rewrite the query so that it 
does more filtering earlier? Your distinct criteria seems to filter it 
down to 20k rows. So maybe it's possible to do some sort of a distinct 
in part of the subselect, before you start joining against other tables.

If you have that much redundancy, you might also need to think of doing 
a different normalization.

Just some thoughts.

Also, I thought using the "oid" column wasn't really recommended, since 
in *high* volume databases they aren't even guaranteed to be unique. (I 
think it is a 32-bit number that rolls over.) Also on a database dump 
and restore, they don't stay the same, unless you take a lot of extra 
care that they are included in both the dump and the restore. I believe 
it is better to create your own "id" per table (say SERIAL or BIGSERIAL).

John
=:->