From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [PERFORM] 7.3.1 index use / performance
Date: Tue, 7 Jan 2003 07:27:49 -0800 (PST)
Message-ID: <20030107072146.L61341-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
On Tue, 7 Jan 2003, Achilleus Mantzios wrote:
> 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 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)
What do the statistics for the three columns actually look like and what
are the real distributions and counts like?
Given an estimated cost of around 4 for the first scan, my guess would be
that it's not expecting alot of rows between 2002-01-07 and 2003-01-07
which would make that a reasonable plan.
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: sszabo@megazone23.bigpanda.com, achill@matrix.gatewaynet.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [PERFORM] 7.3.1 index use / performance
In-Reply-To: <20030107072146.L61341-100000@megazone23.bigpanda.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