pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is This My Speed Limit?
3+ messages / 3 participants
[nested] [flat]

* Is This My Speed Limit?
@ 2003-10-03 02:14 CN <cnliou9@fastmail.fm>
  2003-10-03 05:59 ` Re: Is This My Speed Limit? Tomasz Myrta <jasiek@klaster.net>
  2003-10-03 14:47 ` Re: Is This My Speed Limit? scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 3+ messages in thread

From: CN @ 2003-10-03 02:14 UTC (permalink / raw)
  To: pgsql-performance

Hi!

It's just my curiosity. I wonder if there is any way to break my speed
limit on AMD 450Mhz:

Best Regards,
CN
-------------------
--This table contains 1036 rows.
CREATE TABLE table1 (
c1  VARCHAR(20) PRIMARY KEY,
c2  "char"
)WITHOUT OIDS;
---------------------
--This table contains 9429 rows.
CREATE TABLE table2 (
c1  VARCHAR(20) PRIMARY KEY,
c2  DATE,
c3  INTEGER,
c4  INTEGER
)WITHOUT OIDS;
CREATE INDEX i2c3c4 ON table2 (c3,c4);
---------------------
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk3c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk3c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1  VARCHAR(20),
c2  INTEGER,
c3  VARCHAR(20),
c4  "char",
c5  INTEGER
)WITHOUT OIDS;
---------------------
EXPLAIN ANALYZE
SELECT
  table2.c3 AS year
  ,table2.c4 AS month
  ,(SELECT CASE
      WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
        OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
      THEN table3.c5 ELSE 0-table3.c5 END
    FROM table1
    WHERE table1.c1=table3.c3
   ) AS amount
FROM table2,table3
WHERE table3.c1=table2.c1
  AND table2.c3 > 2001;

 Hash Join  (cost=189.79..1508.67 rows=11203 width=48) (actual
 time=129.20..1780.53 rows=9912 loops=1)
   Hash Cond: ("outer".c1 = "inner".c1)
   ->  Seq Scan on table3  (cost=0.00..822.82 rows=28482 width=27)
   (actual time=14.01..403.78 rows=28482 
loops=1)
   ->  Hash  (cost=180.69..180.69 rows=3640 width=21) (actual
   time=85.61..85.61 rows=0 loops=1)
         ->  Seq Scan on table2  (cost=0.00..180.69 rows=3640 width=21)
         (actual time=0.28..64.62 rows=3599 
loops=1)
               Filter: (c3 > 2001)
   SubPlan
     ->  Index Scan using table1_pkey on table1  (cost=0.00..3.01 rows=1
     width=1) (actual time=0.06..0.06 
rows=1 loops=9912)
           Index Cond: (c1 = $2)
 Total runtime: 1802.71 msec
-------------------
EXPLAIN ANALYZE
SELECT
  table2.c3 AS year
  ,table2.c4 AS month
  ,CASE
      WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
        OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
      THEN table3.c5 ELSE 0-table3.c5 END
      AS amount
FROM table2,table3,table1
WHERE table3.c1=table2.c1
  AND table1.c1=table3.c3
AND table2.c3 > 2001;

 Hash Join  (cost=208.74..1751.68 rows=11203 width=58) (actual
 time=135.87..1113.69 rows=9912 loops=1)
   Hash Cond: ("outer".c3 = "inner".c1)
   ->  Hash Join  (cost=189.79..1508.67 rows=11203 width=48) (actual
   time=123.81..899.29 rows=9912 loops=1)
         Hash Cond: ("outer".c1 = "inner".c1)
         ->  Seq Scan on table3  (cost=0.00..822.82 rows=28482 width=27)
         (actual time=9.30..371.10 rows=28482 
loops=1)
         ->  Hash  (cost=180.69..180.69 rows=3640 width=21) (actual
         time=85.62..85.62 rows=0 loops=1)
               ->  Seq Scan on table2  (cost=0.00..180.69 rows=3640
               width=21) (actual time=0.31..64.33 
rows=3599 loops=1)
                     Filter: (c3 > 2001)
   ->  Hash  (cost=16.36..16.36 rows=1036 width=10) (actual
   time=11.91..11.91 rows=0 loops=1)
         ->  Seq Scan on table1  (cost=0.00..16.36 rows=1036 width=10)
         (actual time=0.05..7.16 rows=1036 
loops=1)
 Total runtime: 1133.95 msec

-- 
http://www.fastmail.fm - Does exactly what it says on the tin



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Is This My Speed Limit?
  2003-10-03 02:14 Is This My Speed Limit? CN <cnliou9@fastmail.fm>
@ 2003-10-03 05:59 ` Tomasz Myrta <jasiek@klaster.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Tomasz Myrta @ 2003-10-03 05:59 UTC (permalink / raw)
  To: CN <cnliou9@fastmail.fm>; +Cc: pgsql-performance

> Hi!
> 
> It's just my curiosity. I wonder if there is any way to break my speed
> limit on AMD 450Mhz:

>  Hash Join  (cost=189.79..1508.67 rows=11203 width=48) (actual
>  time=129.20..1780.53 rows=9912 loops=1)

>  Hash Join  (cost=208.74..1751.68 rows=11203 width=58) (actual
>  time=135.87..1113.69 rows=9912 loops=1)

Well, it looks like a speed limit. I wouldn't expect better speed for
queries returning 10000 rows.

Regards,
Tomasz Myrta




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Is This My Speed Limit?
  2003-10-03 02:14 Is This My Speed Limit? CN <cnliou9@fastmail.fm>
@ 2003-10-03 14:47 ` scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 3+ messages in thread

From: scott.marlowe @ 2003-10-03 14:47 UTC (permalink / raw)
  To: CN <cnliou9@fastmail.fm>; +Cc: pgsql-performance

On Thu, 2 Oct 2003, CN wrote:

> Hi!
> 
> It's just my curiosity. I wonder if there is any way to break my speed
> limit on AMD 450Mhz:

You're most likely I/O bound, not CPU bound here.  So, if you want better 
speed, you'll likely need a better storage subsystem.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-10-03 14:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-03 02:14 Is This My Speed Limit? CN <cnliou9@fastmail.fm>
2003-10-03 05:59 ` Tomasz Myrta <jasiek@klaster.net>
2003-10-03 14:47 ` scott.marlowe <scott.marlowe@ihs.com>

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