SQL stupid query plan... terrible performance !

Hi,

I have one performance issue... and realy have no idea what's going on...
When I set enable_seqscan to 0, query2 runs the same way...

upload      =>  60667 entities
uploadfield => 506316 entities

Query1:
select count(*) from Upload NATURAL JOIN UploadField Where Upload.ShopID 
= 123123;

181.944 ms

Query2:
select count(*) from Upload NATURAL JOIN UploadField Where 
Upload.UploadID = 123123;

1136.024 ms

Greetings,
Jim J.


-------
Details:
PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 
20030222 (Red Hat Linux 3.2.2-5)

                                                    QUERY1 PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=1972.50..1972.50 rows=1 width=0) (actual 
time=181.657..181.658 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..1972.46 rows=17 width=0) (actual 
time=181.610..181.610 rows=0 loops=1)
        ->  Seq Scan on upload  (cost=0.00..1945.34 rows=2 width=8) 
(actual time=181.597..181.597 rows=0 loops=1)
              Filter: (shopid = 123123)
        ->  Index Scan using relationship_3_fk on uploadfield  
(cost=0.00..13.44 rows=10 width=8) (never executed)
              Index Cond: ("outer".uploadid = uploadfield.uploadid)
Total runtime: 181.944 ms

                                                    QUERY2 PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=15886.74..15886.74 rows=1 width=0) (actual 
time=1135.804..1135.806 rows=1 loops=1)
  ->  Nested Loop  (cost=1945.34..15886.69 rows=20 width=0) (actual 
time=1135.765..1135.765 rows=0 loops=1)
        ->  Seq Scan on uploadfield  (cost=0.00..13940.95 rows=10 
width=8) (actual time=1135.754..1135.754 rows=0 loops=1)
              Filter: (123123 = uploadid)
        ->  Materialize  (cost=1945.34..1945.36 rows=2 width=8) (never 
executed)
              ->  Seq Scan on upload  (cost=0.00..1945.34 rows=2 
width=8) (never executed)
                    Filter: (uploadid = 123123)
Total runtime: 1136.024 ms


             Table "public.upload"
  Column   |          Type          | Modifiers
------------+------------------------+-----------
uploadid   | bigint                 | not null
nativedb   | text                   | not null
shopid     | bigint                 | not null
Indexes:
   "pk_upload" primary key, btree (uploadid)
   "nativedb" btree (nativedb)
   "uploadshopid" btree (shopid)

     Table "public.uploadfield"
   Column     |   Type   | Modifiers
---------------+----------+-----------
uploadfieldid | bigint   | not null
fieldnameid   | smallint | not null
uploadid      | bigint   | not null

Indexes:
   "pk_uploadfield" primary key, btree (uploadfieldid)
   "relationship_3_fk" btree (uploadid)
   "relationship_4_fk" btree (fieldnameid)
Foreign-key constraints:
   "fk_uploadfi_fieldname_fieldnam" FOREIGN KEY (fieldnameid) 
REFERENCES fieldname(fieldnameid) ON UPDATE RESTRICT ON DELETE RESTRICT
   "fk_uploadfi_uploadfie_upload" FOREIGN KEY (uploadid) REFERENCES 
upload(uploadid) ON UPDATE RESTRICT ON DELETE RESTRICT


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org