Using Bitmap scan instead of Seq scan

Поиск
Список
Период
Сортировка
От Ahmed Ossama
Тема Using Bitmap scan instead of Seq scan
Дата
Msg-id 4D5968FF.70208@aossama.net
обсуждение исходный текст
Ответы Re: Using Bitmap scan instead of Seq scan  ("David Johnston" <polobo@yahoo.com>)
Re: Using Bitmap scan instead of Seq scan  (Vick Khera <vivek@khera.org>)
Список pgsql-general
Greetings guys,

I am running PostgreSQL 8.4, my database is 60GB. My problem is that
there are some queries run extremely fast (when bitmap scan is used),
while others take more than 600secs to respond (when seq scan is used).

Here is a sample of the queries which uses seq scan...

[2011-02-14 09:15:45.464 PST] <schema1> 6828: LOG:  duration: 620577.665
ms  plan:
         Sort  (cost=1530070.15..1530072.12 rows=790 width=1539) (actual
time=620453.962..620458.985 rows=3404 loops=1)
           Sort Key: members.state, (ts_rank(members.ftsearch,
to_tsquery('personal & care & Barbers'::text)))
           Sort Method:  quicksort  Memory: 2103kB
           ->  Seq Scan on members  (cost=0.00..1530032.12 rows=790
width=1539) (actual time=47559.697..620422.036 rows=3404 loops=1)
                 Filter: (((edited_time IS NOT NULL) OR
((member_type)::text = 'paid_business'::text)) AND (ftsearch @-@
to_tsquery('personal & care & Barbers'::text)) AND (ts_rank(ftsearch,
to_tsquery('personal & care & Barbers'::text)) > 0.3::double precision))
[2011-02-14 09:15:45.464 PST] <schema1> 6828: STATEMENT:  SELECT * FROM
(SELECT ts_rank(ftsearch, to_tsquery('personal & care & Barbers')) as

this_rank,members.status,members.business_hours,members.keywords01,members.first_name,members.last_name,members.address01,members.city,members.state,members.address02,members.asn_member_id,members.keywords02,members.postal_code,members.phone01,members.tel_index,members.member_type,members.easn_member_id,members.keywords03,members.keywords04,members.keywords05,members.blurb_title,members.blurb_text,members.web_address,members.main_category,members.job_name02,members.company

from schema1.members  WHERE ftsearch @-@ to_tsquery('personal & care &
Barbers') and (edited_time is not null or member_type =
'paid_business')) as t1 WHERE this_rank > 0.3 ORDER BY state, this_rank
DESC;

I did a REINDEX and ANALYZE on the table, disabled the seqscan and ran
the query again with the same result.

My question how do I make PostgreSQL always uses bitmap scan instead of
seq scan?

Any advice is very much appreciated.

Best Regards,
Ahmed Ossama

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Revoking Function Execute Privilege
Следующее
От: "David Johnston"
Дата:
Сообщение: Re: Using Bitmap scan instead of Seq scan