Обсуждение: select count(*) is slow
Hi,
Below query takes 12 seconds. We have an index on postcode.
select count(*) from table where postcode >= '00420' AND postcode <= '00500'
index:
CREATE INDEX Table_i1
ON table USING btree
((postcode::numeric));
ON table USING btree
((postcode::numeric));
Table has 180,000 rows and the count is 150,000. Expectation is to run this query in 2-3 seconds(it takes 2 seconds in Oracle).
Here is a query plan:
"Aggregate (cost=622347.34..622347.35 rows=1 width=8) (actual time=12850.580..12850.580 rows=1 loops=1)"
" -> Bitmap Heap Scan on table (cost=413379.89..621681.38 rows=266383 width=0) (actual time=12645.656..12835.185 rows=209749 loops=1)"
" Recheck Cond: (((postcode)::text >= '00420'::text) AND ((postcode)::text <= '00500'::text))"
" Heap Blocks: exact=118286"
" -> Bitmap Index Scan on table_i4 (cost=0.00..413313.29 rows=266383 width=0) (actual time=12615.321..12615.321 rows=209982 loops=1)"
" Index Cond: (((postcode)::text >= '00420'::text) AND ((postcode)::text <= '00500'::text))"
"Planning Time: 0.191 ms"
"Execution Time: 12852.823 ms"
Regards,
Aditya.
aditya desai <admad123@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.
regards, tom lane
Thanks Tom. Will try with numeric. Please ignore table and index naming.
On Tue, Apr 6, 2021 at 6:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
aditya desai <admad123@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.
regards, tom lane
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@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > aditya desai <admad123@gmail.com <mailto:admad123@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
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@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 <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> aditya desai <admad123@gmail.com <mailto:admad123@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