Thread: Using Bitmap scan instead of Seq scan
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
You cannot ALWAYS do an indexed scan - sometimes the only option for the parser is to do a sequential scan (thus you can say "avoid unless you have to" but you can never truly disable sequential scanning). Given limited knowledge of full-text searching I cannot explain why this specific query is unable (or unwilling) to use an index. Since you have not provided any index options for the list to consider we may even assume you have NO indexes and thus none to choose from... I'd suggest looking at chapters 11 (Indexes) and 12 (Full-Text) of the documentation while awaiting further responses from the list (after you describe what indexes you have already defined on relevant tables - and maybe provide the raw query as well). David J -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ahmed Ossama Sent: Monday, February 14, 2011 12:40 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Using Bitmap scan instead of Seq scan 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Feb 14, 2011 at 11:57 AM, David Johnston <polobo@yahoo.com> wrote:
Does this suggest that the config items "disable_seqscan" (and friends) should be renamed to "avoid_seqscan" ?
-- You cannot ALWAYS do an indexed scan - sometimes the only option for the
parser is to do a sequential scan (thus you can say "avoid unless you have
to" but you can never truly disable sequential scanning).
Does this suggest that the config items "disable_seqscan" (and friends) should be renamed to "avoid_seqscan" ?
Jon
Jon Nelson <jnelson+pgsql@jamponi.net> writes: > Does this suggest that the config items "disable_seqscan" (and friends) > should be renamed to "avoid_seqscan" ? Some of them actually are disable, some are only avoid. The documentation says which is which, but I don't think creating a distinction at the naming level would be helpful (especially since the planner's behavior has been known to change from time to time). regards, tom lane
On Mon, Feb 14, 2011 at 12:40 PM, Ahmed Ossama <ahmed@aossama.net> wrote: > 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. > Have a look at the config parameters related to sequential page cost and random page cost. Perhaps you need to adjust the ratio between them to better suit your hardware. The planner may nudge itself over to the index scan if it thinks it won't be too expensive. Also, I'm not sure the default sample size for analyze in 8.4, but it should probably be set to 100 if it is not already.