From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-performance@postgresql.org, <pgsql-general@postgresql.org>
To: pgsql-sql@postgresql.org
Subject: 7.3.1 index use / performance
Date: Tue, 7 Jan 2003 13:39:57 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com> (raw)
Hi,
i am just in the stage of having migrated my test system to 7.3.1
and i am experiencing some performance problems.
i have a table "noon"
Table "public.noon"
Column | Type | Modifiers
------------------------+------------------------+-----------
v_code | character varying(4) |
log_no | bigint |
report_date | date |
report_time | time without time zone |
voyage_no | integer |
charterer | character varying(12) |
port | character varying(24) |
duration | character varying(4) |
rotation | character varying(9) |
......
with a total of 278 columns.
it has indexes:
Indexes: noonf_date btree (report_date),
noonf_logno btree (log_no),
noonf_rotation btree (rotation text_ops),
noonf_vcode btree (v_code),
noonf_voyageno btree (voyage_no)
On the test 7.3.1 system (a FreeBSD 4.7-RELEASE-p2, Celeron 1.2GHz
400Mb, with 168Mb for pgsql),
i get:
dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON ' and report_date between
'2002-01-07' and '2003-01-07';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using noonf_date on noon (cost=0.00..4.46 rows=1 width=39)
(actual time=0.27..52.89 rows=259 loops=1)
Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
'2003-01-07'::date))
Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON
'::character varying))
Total runtime: 53.98 msec
(4 rows)
after i drop the noonf_date index i actually get better performance
cause the backend uses now the more appropriate index noonf_vcode :
dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON ' and report_date between
'2002-01-07' and '2003-01-07';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using noonf_vcode on noon (cost=0.00..3122.88 rows=1
width=39) (actual time=0.16..13.92 rows=259 loops=1)
Index Cond: (v_code = '4500'::character varying)
Filter: ((rotation = 'NOON '::character varying) AND (report_date
>= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
Total runtime: 14.98 msec
(4 rows)
On the pgsql 7.2.3 development system (a RH linux 2.4.7, PIII 1 GHz,
1Mb, with 168M for pgsql), i always get the right index use:
dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON ' and report_date between
'2002-01-07' and '2003-01-07';
NOTICE: QUERY PLAN:
Index Scan using noonf_vcode on noon (cost=0.00..3046.38 rows=39
width=39) (actual time=0.09..8.55 rows=259 loops=1)
Total runtime: 8.86 msec
EXPLAIN
Is something i am missing??
Is this reasonable behaviour??
P.S.
Yes i have vaccumed analyzed both systems before the queries were issued.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, pgsql-general@postgresql.org
Subject: Re: 7.3.1 index use / performance
In-Reply-To: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
* 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