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 <and...@dunslane.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 <t...@sss.pgh.pa.us > > <mailto:t...@sss.pgh.pa.us>> wrote: > > > > aditya desai <admad...@gmail.com <mailto:admad...@gmail.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 > >
