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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> <mailto:tgl@xxxxxxxxxxxxx>> wrote:
>
> aditya desai <admad123@xxxxxxxxx <mailto:admad123@xxxxxxxxx>> 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
