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
