Thread: 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
On Jun 27, 2004, at 8:37 PM, Jim wrote: > 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 > Have you vacuum analyze'd recently? -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Jim <jim.jim@wp.pl> writes:
> I have one performance issue... and realy have no idea what's going on...
[yawn...] Cast the constants to bigint. See previous discussions.
regards, tom lane
On Sun, 27 Jun 2004 23:29:46 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jim <jim.jim@wp.pl> writes: > > I have one performance issue... and realy have no idea what's going on... > > [yawn...] Cast the constants to bigint. See previous discussions. > > regards, tom lane Would there be any way of adding some sort of indicator to the plan as to why sequential was chosen? eg Seq Scan on upload (type mismatch) (cost....) Seq Scan on upload (statistics) (cost....) Seq Scan on upload (catch-all) (cost....) klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
Klint Gore <kg@kgb.une.edu.au> writes:
> On Sun, 27 Jun 2004 23:29:46 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> [yawn...] Cast the constants to bigint. See previous discussions.
> Would there be any way of adding some sort of indicator to the plan as
> to why sequential was chosen?
Not really ... the plan that's presented is the one that looked the
cheapest out of the feasible plans. How are you going to identify a
single reason as to why any other plan was not generated or lost out
on a cost-estimate basis? Humans might be able to do so (note that
the above quote is an off-the-cuff estimate, not something I'd care
to defend rigorously) but I don't think software can do it.
FWIW, the particular problem here should be fixed in 7.5.
regards, tom lane
2004-06-28 07:48, Tom Lane wrote: >Klint Gore <kg@kgb.une.edu.au> writes: >> On Sun, 27 Jun 2004 23:29:46 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> [yawn...] Cast the constants to bigint. See previous discussions. > > [cuuuut] Thanks a lot guys. The term "Cast the constants to bigint" It is what I was looking for. I add explicitly ::data_type in my queries and everything works fine now. One more thanks to Tom Lane - After your answer I found your post on the newsgroup about this problem... the date of the post is 2001 year... You are really patience man.... :) But I really have no idea what term I could use to force goggle to give me solution ;) Greetings, Jim J.