SQL stupid query plan... terrible performance ! - Mailing list pgsql-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