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 wrote: > > On 4/6/21 9:30 AM, aditya desai wrote: > > Thanks Tom. Will try with numeric. Please ignore table and index naming. > > > >

Re: select count(*) is slow

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 > wrote: > > aditya desai mailto:admad...@gmail.com>> writes: > > Below query takes 12 seconds. We have

Re: select count(*) is slow

Thanks Tom. Will try with numeric. Please ignore table and index naming. On Tue, Apr 6, 2021 at 6:55 PM Tom Lane wrote: > aditya desai writes: > > Below query takes 12 seconds. We have an index on postcode. > > > select count(*) from table where postcode >= '00420' AND postcode <= > '00500' >

Re: select count(*) is slow

aditya desai 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 ei