select count(*) is slow

Поиск
Список
Период
Сортировка
От aditya desai
Тема select count(*) is slow
Дата
Msg-id CAN0SRDFFCKvS0gv_bnn2Pg=G7eu7GAu+b0yeariVpuhQ_ewQXg@mail.gmail.com
обсуждение исходный текст
Ответы Re: select count(*) is slow  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Substitute for synonym in Oracle after migration to postgres
Следующее
От: aditya desai
Дата:
Сообщение: Re: Substitute for synonym in Oracle after migration to postgres