Обсуждение: Help on my database 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

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

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

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