pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Alfranio Junior <alfranio@lsd.di.uminho.pt>
To: pgsql-performance@postgresql.org
Subject: PERFORMANCE and SIZE
Date: Mon, 12 May 2003 12:35:24 -0700
Message-ID: <011d01c318be$997217f0$9002a8c0@ialfranio> (raw )
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
view thread (8+ messages) latest in thread
Message-ID: <011d01c318be$997217f0$9002a8c0@ialfranio>
Permalink: ../011d01c318be$997217f0$9002a8c0@ialfranio/
Also on: postgresql.org/message-id/011d01c318be$997217f0$9002a8c0@ialfranio
copy link · copy postgr.es
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: alfranio@lsd.di.uminho.pt
Subject: Re: PERFORMANCE and SIZE
In-Reply-To: <011d01c318be$997217f0$9002a8c0@ialfranio>
References: <011d01c318be$997217f0$9002a8c0@ialfranio>
* 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