pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim <jim.jim@wp.pl>
To: pgsql-performance@postgresql.org
Subject: SQL stupid query plan... terrible performance !
Date: Mon, 28 Jun 2004 02:37:33 +0200
Message-ID: <40DF684D.9060207@wp.pl> (raw)

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




view thread (6+ messages)  latest in thread

Message-ID: <40DF684D.9060207@wp.pl>
Permalink:  ../40DF684D.9060207@wp.pl/
Also on:    postgresql.org/message-id/40DF684D.9060207@wp.pl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jim.jim@wp.pl
  Subject: Re: SQL stupid query plan... terrible performance !
  In-Reply-To: <40DF684D.9060207@wp.pl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox