Re: select count(*) is slow

Thanks to all of you. Removed casting to numeric from Index. Performance
improved from 12 sec to 500 ms. Rocket!!!

On Tue, Apr 6, 2021 at 9:14 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 4/6/21 9:30 AM, aditya desai wrote:
> > Thanks Tom. Will try with numeric. Please ignore table and index naming.
> >
> > On Tue, Apr 6, 2021 at 6:55 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> >
> > aditya desai <admad123(at)gmail(dot)com <mailto:admad123(at)gmail(dot)com>>
> writes:
> > > Below query takes 12 seconds. We have an index on postcode.
> >
> > > select count(*) from table where postcode >= '00420' AND
> > postcode <= '00500'
> >
> > That query does not match this index:
> >
> > > CREATE INDEX Table_i1
> > > ON table USING btree
> > > ((postcode::numeric));
> >
> > You could either change postcode to numeric, change all your queries
> > of this sort to include the cast explicitly, or make an index that
> > doesn't have a cast.
> >
> >
> >
>
>
> IMNSHO postcodes, zip codes, telephone numbers and the like should never
> be numeric under any circumstances. This isn't numeric data (what is the
> average postcode?), it's textual data consisting of digits, so they
> should always be text/varchar. The index here should just be on the
> plain text column, not cast to numeric.
>
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>