From: | aditya desai <admad123(at)gmail(dot)com> |
---|---|
To: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | select count(*) is slow |
Date: | 2021-04-06 13:14:02 |
Message-ID: | CAN0SRDFFCKvS0gv_bnn2Pg=G7eu7GAu+b0yeariVpuhQ_ewQXg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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));
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.
From | Date | Subject | |
---|---|---|---|
Next Message | aditya desai | 2021-04-06 13:19:16 | Re: Substitute for synonym in Oracle after migration to postgres |
Previous Message | hubert depesz lubaczewski | 2021-04-06 10:41:48 | Re: Substitute for synonym in Oracle after migration to postgres |