Thread: SQL stupid query plan... terrible performance !

SQL stupid query plan... terrible performance !

From
Jim
Date:
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


Re: SQL stupid query plan... terrible performance !

From
Jeff
Date:
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/


Re: SQL stupid query plan... terrible performance !

From
Tom Lane
Date:
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

Re: SQL stupid query plan... terrible performance !

From
Klint Gore
Date:
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             :                 :
+---------------------------------------+-----------------+

Re: SQL stupid query plan... terrible performance !

From
Tom Lane
Date:
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

Re: SQL stupid query plan... terrible performance !

From
Jim
Date:
 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.