Re: Trouble with index in 7.1

That wasn't it.  Largest occurance was 96, and a few 95's, in a table of
800,000.   But, as other posts have said, ANALYZE fixed the problem.

Thanks for the hint!

Gerry




        
                      "Tom Lane"
        
                      <tgl@sss.pgh.pa.         To:      gerry.smit@lombard.ca
        
                      us>                      cc:      pgsql-novice@postgresql.org
        
                                               Fax to:
        
                      16/05/2002 12:32         Subject: Re: [NOVICE] Trouble with index in 7.1
        
                      AM
        

        

        




gerry.smit@lombard.ca writes:
> Hi folks. We're having a baffling time with Postgres 7.1

Updating to 7.2 might help.

> cen_db=> explain select * from pol_xref_d where policy_no=1200079;

> Seq Scan on pol_xref_d  (cost=0.00..22299.22 rows=8325 width=80)

> Sequential Scan  !!!!  Good God, why ?

Because it thinks a substantial fraction of your table will be visited
(note the rows=8325 estimate).  My guess is that you have some one value
of policy_no that is extremely common, and that that is fooling the
7.1 planner into thinking the table contains only a few distinct values
of policy_no --- which would make a seqscan an appropriate choice.

7.2 keeps more extensive stats and is more able to deal with scenarios
where there are a few common values and lots of not-so-common values.

   regards, tom lane