Re: [PERFORM] 7.3.1 index use / performance

On Tue, 7 Jan 2003, Tom Lane wrote:

> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > About the stats on these 3 columns i get:
>
> Does 7.2 generate the same stats?  (minus the schemaname of course)

Not absolutely but close:

(See attachment)

>
> Also, I would like to see the results of these queries on both versions,
> so that we can see what the planner thinks the index selectivity is:
>
> EXPLAIN ANALYZE select * from noon where
> v_code='4500';
>
> EXPLAIN ANALYZE select * from noon where
> report_date between '2002-01-07' and '2003-01-07';
>

On 7.3.1 (On a FreeBSD)
=======================
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
                                                         QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_vcode on noon  (cost=0.00..3066.64 rows=829
width=1974) (actual time=2.02..1421.14 rows=792 loops=1)
   Index Cond: (v_code = '4500'::character varying)
 Total runtime: 1424.82 msec
(3 rows)


dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
                                                           QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_date on noon  (cost=0.00..15919.50 rows=11139
width=1974) (actual time=2.05..13746.17 rows=7690 loops=1)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
'2003-01-07'::date))
 Total runtime: 13775.48 msec
(3 rows)

On 7.2.3 (Linux)
==================
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
NOTICE:  QUERY PLAN:

Index Scan using noonf_vcode on noon  (cost=0.00..3043.45 rows=827
width=1974) (actual time=19.59..927.06 rows=792 loops=1)
Total runtime: 928.86 msec

dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
NOTICE:  QUERY PLAN:

Index Scan using noonf_date on noon  (cost=0.00..16426.45 rows=11958
width=1974) (actual time=29.64..8854.05 rows=7690 loops=1)
Total runtime: 8861.90 msec

EXPLAIN

>             regards, tom lane
>

==================================================================
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