Re: [PERFORM] 7.3.1 index use / performance

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.