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> wrote:
> aditya desai <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.
>
> regards, tom lane
>