pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help on my database performance
4+ messages / 2 participants
[nested] [flat]

* Help on my database performance
@ 2003-07-31 15:06 Jianshuo Niu <jniu@wc-group.com>
  2003-07-31 19:13 ` Re: Help on my database performance Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 1 reply; 4+ messages in thread

From: Jianshuo Niu @ 2003-07-31 15:06 UTC (permalink / raw)
  To: pgsql-performance

Hi! There:

I ran the same explain analyze on two similar tables. However, the table
with less data took much more time than the one with more data. Could anyone
tell me what happened?
Here is the explain analyze:
explain analyze select productid from tfd_catalog;
NOTICE:  QUERY PLAN:

Seq Scan on tfd_catalog  (cost=0.00..43769.82 rows=161282 width=10) (actual
time
=3928.64..12905.76 rows=161282 loops=1)
Total runtime: 13240.21 msec


explain analyze select productid from hm_catalog;
NOTICE:  QUERY PLAN:

Seq Scan on hm_catalog  (cost=0.00..22181.18 rows=277518 width=9) (actual
time=2
1.32..6420.76 rows=277518 loops=1)
Total runtime: 6772.95 msec

Thank you for your help

Josh






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

* Re: Help on my database performance
  2003-07-31 15:06 Help on my database performance Jianshuo Niu <jniu@wc-group.com>
@ 2003-07-31 19:13 ` Manfred Koizar <mkoi-pg@aon.at>
  2003-07-31 20:08   ` Re: Help on my database performance Jianshuo Niu <jniu@wc-group.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Manfred Koizar @ 2003-07-31 19:13 UTC (permalink / raw)
  To: Jianshuo Niu <jniu@wc-group.com>; +Cc: pgsql-performance

On Thu, 31 Jul 2003 11:06:09 -0400, "Jianshuo Niu" <jniu@wc-group.com>
wrote:
>I ran the same explain analyze on two similar tables. However, the table
>with less data took much more time than the one with more data. Could anyone
>tell me what happened?

>Seq Scan on tfd_catalog  (cost=0.00..43769.82 rows=161282 width=10) (actual
>time=3928.64..12905.76 rows=161282 loops=1)
>Total runtime: 13240.21 msec
>
>Seq Scan on hm_catalog  (cost=0.00..22181.18 rows=277518 width=9) (actual
>time=21.32..6420.76 rows=277518 loops=1)
>Total runtime: 6772.95 msec

The first SELECT takes almost twice the time because tfd_catalog has
almost twice as many pages than hm_catalog.  This may be due to having
wider tuples or more dead tuples in tfd_catalog.

In the former case theres not much you can do.

But the high startup cost of the first SELECT is a hint for lots of
dead tuples.  So VACUUM FULL ANALYSE might help.

Servus
 Manfred



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

* Re: Help on my database performance
  2003-07-31 15:06 Help on my database performance Jianshuo Niu <jniu@wc-group.com>
  2003-07-31 19:13 ` Re: Help on my database performance Manfred Koizar <mkoi-pg@aon.at>
@ 2003-07-31 20:08   ` Jianshuo Niu <jniu@wc-group.com>
  2003-07-31 21:32     ` Re: Help on my database performance Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 1 reply; 4+ messages in thread

From: Jianshuo Niu @ 2003-07-31 20:08 UTC (permalink / raw)
  To: pgsql-performance

Dear Manfred:

Thank you so much for your response. vacuum full anaylze works!
explain analyze select count(*) from tfd_catalog ;
NOTICE:  QUERY PLAN:

explain analyze select count(*) from tfd_catalog ;
NOTICE:  QUERY PLAN:

Aggregate  (cost=15986.02..15986.02 rows=1 width=0) (actual
time=1089.99..1089.9
9 rows=1 loops=1)
  ->  Seq Scan on tfd_catalog  (cost=0.00..15582.82 rows=161282 width=0)
(actual
 time=0.11..833.41 rows=161282 loops=1)
Total runtime: 1090.51 msec

EXPLAIN  ->  Seq Scan on tfd_catalog  (cost=0.00..15582.82 rows=161282
width=0) (actual
 time=0.11..833.41 rows=161282 loops=1)
Total runtime: 1090.51 msec

Could you tell me what does  "Aggregate  (cost=15986.02..15986.02 rows=1
width=0) (actual time=1089.99..1089.99 rows=1 loops=1)" mean? It does not
show in my previous report.

I appreicate it.


Josh






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

* Re: Help on my database performance
  2003-07-31 15:06 Help on my database performance Jianshuo Niu <jniu@wc-group.com>
  2003-07-31 19:13 ` Re: Help on my database performance Manfred Koizar <mkoi-pg@aon.at>
  2003-07-31 20:08   ` Re: Help on my database performance Jianshuo Niu <jniu@wc-group.com>
@ 2003-07-31 21:32     ` Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 0 replies; 4+ messages in thread

From: Manfred Koizar @ 2003-07-31 21:32 UTC (permalink / raw)
  To: Jianshuo Niu <jniu@wc-group.com>; +Cc: pgsql-performance

On Thu, 31 Jul 2003 16:08:11 -0400, "Jianshuo Niu" <jniu@wc-group.com>
wrote:
>explain analyze select count(*) from tfd_catalog ;
>NOTICE:  QUERY PLAN:
>
>Aggregate  (cost=15986.02..15986.02 rows=1 width=0)
>           (actual time=1089.99..1089.99 rows=1 loops=1)
>  ->  Seq Scan on tfd_catalog  (cost=0.00..15582.82 rows=161282 width=0)
>                               (actual time=0.11..833.41 rows=161282 loops=1)
>Total runtime: 1090.51 msec

>Could you tell me what does  "Aggregate  (cost=15986.02..15986.02 rows=1
>width=0) (actual time=1089.99..1089.99 rows=1 loops=1)" mean? It does not
>show in my previous report.

In your first post you did 
	SELECT productid FROM tfd_catalog;

now you did
	SELECT count(*) FROM tfd_catalog;

count() is an aggregate function which in your case takes 161282 rows
as input and produces a single row as output.  The "actual" part of
the "Aggregate" line tells you that the first resulting row is
generated 1089.99 milliseconds after query start and the last row (not
surprisingly) at the same time.  The "cost" part contains the
planner's estimations for these values.

Servus
 Manfred



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


end of thread, other threads:[~2003-07-31 21:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-31 15:06 Help on my database performance Jianshuo Niu <jniu@wc-group.com>
2003-07-31 19:13 ` Manfred Koizar <mkoi-pg@aon.at>
2003-07-31 20:08   ` Jianshuo Niu <jniu@wc-group.com>
2003-07-31 21:32     ` Manfred Koizar <mkoi-pg@aon.at>

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