Re: PERFORMANCE and SIZE - Mailing list pgsql-performance

Alfranio Junior,

99% likely:  You ran the second query after the first
and the 4 result rows where already stored in memory.
The first execution took longer because the database
had to go to the disk after looking up in the index
what rows to get.  I further assume that the index was
already in memory for both queries since you most
likely just build it.

Of course you also need to vaccuum on a regular basis
in order to have up to date statstics.

Regards,
Nikolaus Dilger


On Mon, 12 May 2003 12:35:24 -0700, "Alfranio Junior"
wrote:

>
>  Hello,
>
> I'm a new PostgresSql user and I do not know so much
> about the
>  performance mechanisms currently implemented and
> available.
>
>  So, as a dummy user I think that something strange is
> happening with me.
>  When I run the following command:
>
>  explain analyze select * from customer
>        where c_last = 'ROUGHTATION' and
>        c_w_id = 1 and
>        c_d_id = 1
>        order by c_w_id, c_d_id, c_last, c_first limit
1;
>
>  I receive the following results:
>
>  (Customer table with 60.000 rows) -
>

>         QUERY PLAN
>
---------------------------------------------------------------------------
>
-----------------------------------------------------------
>    Limit  (cost=4.84..4.84 rows=1 width=283) (actual
> time=213.13..213.13
>  rows=0 loops=1)
>      ->  Sort  (cost=4.84..4.84 rows=1 width=283)
> (actual
>  time=213.13..213.13 rows=0 loops=1)
>            Sort Key: c_w_id, c_d_id, c_last, c_first
>            ->  Index Scan using pk_customer on
customer
>  (cost=0.00..4.83
>  rows=1 width=283) (actual time=211.93..211.93 rows=0
> loops=1)
>                  Index Cond: ((c_w_id = 1) AND (c_d_id
> = 1))
>                  Filter: (c_last =
> 'ROUGHTATION'::bpchar)
>    Total runtime: 213.29 msec
>  (7 rows)
>
>
>  (Customer table with 360.000 rows) -
>

>          QUERY PLAN
>
---------------------------------------------------------------------------
>
-------------------------------------------------------------
>    Limit  (cost=11100.99..11101.00 rows=1 width=638)
> (actual
>  time=20.82..20.82 rows=0 loops=1)
>      ->  Sort  (cost=11100.99..11101.00 rows=4
> width=638) (actual
>  time=20.81..20.81 rows=0 loops=1)
>            Sort Key: c_w_id, c_d_id, c_last, c_first
>            ->  Index Scan using pk_customer on
customer
>  (cost=0.00..11100.95 rows=4 width=638) (actual
> time=20.40..20.40 rows=0
>  loops=1)
>                  Index Cond: ((c_w_id = 1) AND (c_d_id
> = 1))
>                  Filter: (c_last =
> 'ROUGHTATION'::bpchar)
>    Total runtime: 21.11 msec
>  (7 rows)
>
>  Increasing the number of rows the total runtime
> decreases.
>  The customer table has the following structure:
>  CREATE TABLE customer
>  (
>       c_id int NOT NULL ,
>       c_d_id int4 NOT NULL ,
>       c_w_id int4 NOT NULL ,
>       c_first char (16) NULL ,
>       c_middle char (2) NULL ,
>       c_last char (16) NULL ,
>       c_street_1 char (20) NULL ,
>       c_street_2 char (20) NULL ,
>       c_city char (20) NULL ,
>       c_state char (2) NULL ,
>       c_zip char (9) NULL ,
>       c_phone char (16) NULL ,
>       c_since timestamp NULL ,
>       c_credit char (2) NULL ,
>       c_credit_lim numeric(12, 2) NULL ,
>       c_discount numeric(4, 4) NULL ,
>       c_balance numeric(12, 2) NULL ,
>       c_ytd_payment numeric(12, 2) NULL ,
>       c_payment_cnt int4 NULL ,
>       c_delivery_cnt int4 NULL ,
>       c_data text NULL
>  );
>
>  ALTER TABLE customer  ADD
>       CONSTRAINT PK_customer PRIMARY KEY
>       (
>           c_w_id,
>           c_d_id,
>           c_id
>       );
>
>  Does anybody know what is happening ?
>
>
>  Thanks !!!!
>
>  Alfranio Junior
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html