Re: Beginner Question - Mailing list pgsql-performance

Yeah, I have a lot of similar problems where an index that I have to
speed up one query is used in another query where it actually slows
it down.  Is there any way to ignore indexes for certain queries?
We've been appending empty strings and adding zero's to the column
data to force it into a filter, but it's a messy hack.  I've tried
ordering the joins in the the most efficent way with a
join_collapse_limit of 1, but it still does uses this index in
parallel with searching an index on another table (i guess the
planner figures it's saving some time up front).

-Mike

On Apr 9, 2007, at 8:45 PM, s d wrote:

> Hi Jan,
> Adding this Index slowed down things by a factor of 4.
>
> Also, the performance is so horrible (example bellow) that i am
> certain i am doing something wrong.
>
> Does the following explain gives any ideas ?
>
> Thanks
>
> =#  EXPLAIN ANALYZE select * from word_association where (word1 ='the'
> or word2='the') and count > 10;
>
> QUERY PLAN
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> ----
> Bitmap Heap Scan on word_association  (cost=250.86..7256.59 rows=4624
> width=22) (actual time=13.461..211.568 rows=6601 loops=1)
>   Recheck Cond: (((word1)::text = 'the'::text) OR ((word2)::text =
> 'the'::text))
>   Filter: (count > 10)
>   ->  BitmapOr  (cost=250.86..250.86 rows=12243 width=0) (actual
> time=9.052..9.052 rows=0 loops=1)
>         ->  Bitmap Index Scan on word_association_index1_1
> (cost=0.00..153.20 rows=7579 width=0) (actual time=5.786..5.786
> rows=7232 loops=1)
>               Index Cond: ((word1)::text = 'the'::text)
>         ->  Bitmap Index Scan on word_association_index2_1
> (cost=0.00..95.34 rows=4664 width=0) (actual time=3.253..3.253
> rows=4073 loops=1)
>               Index Cond: ((word2)::text = 'the'::text)
>  Total runtime: 219.987 ms
> (9 rows)
>
>
> On 4/9/07, Jan de Visser <jdevisser@digitalfairway.com> wrote:
>> On Monday 09 April 2007 05:09:53 s d wrote:
>> > Hi,
>> > I am trying to figure out how to debug a performance problem /
>> use psql
>> > explain. The table in question is:
>> > # \d word_association;
>> >            Table "public.word_association"
>> >  Column |          Type          |     Modifiers
>> > --------+------------------------+--------------------
>> >  word1  | character varying(128) | not null
>> >  word2  | character varying(128) | not null
>> >  count  | integer                | not null default 0
>> > Indexes:
>> >     "word1_word2_comb_unique" unique, btree (word1, word2)
>> >     "word1_hash_index" hash (word1)
>> >     "word2_hash_index" hash (word2)
>> >     "word_association_count_index" btree (count)
>> >     "word_association_index1_1" btree (word1)
>> >     "word_association_index2_1" btree (word2)
>> >
>> > It has multiple indices since i wanted to see which one the
>> planner choses.
>> >
>> >
>> > # explain select * FROM word_association WHERE (word1 = 'bdss' OR
>> > word2 = 'bdss')  AND count >= 10;
>> >                                            QUERY PLAN
>> >
>> ---------------------------------------------------------------------
>> ------
>> >--------------------- Bitmap Heap Scan on word_association
>> > (cost=11.53..1192.09 rows=155 width=22) Recheck Cond:
>> (((word1)::text =
>> > 'bdss'::text) OR ((word2)::text = 'bdss'::text))
>> >    Filter: (count >= 10)
>> >    ->  BitmapOr  (cost=11.53..11.53 rows=364 width=0)
>> >          ->  Bitmap Index Scan on word_association_index1_1
>> > (cost=0.00..5.79 rows=190 width=0)
>> >                Index Cond: ((word1)::text = 'bdss'::text)
>> >          ->  Bitmap Index Scan on word_association_index2_1
>> > (cost=0.00..5.67 rows=174 width=0)
>> >                Index Cond: ((word2)::text = 'bdss'::text)
>> > (8 rows)
>> >
>> > The questions:
>> > 1. i can undestand where the cost=11.53 came from but where did the
>> > 1192.09 come form? The values are in milli right ?
>> > 2. the query takes  in reality much longer than 1 second.
>> >
>> > In short, it feels like something is very wrong here (i tried
>> vacuum
>> > analyze and it didn't do much diff).
>> > any ideas ?
>>
>> You need an index on (word1, word2, count). In your current setup
>> it will have
>> to scan all rows that satisfy word1 and word2 to see if count >= 10.
>>
>> jan
>>
>>
>> --
>> --------------------------------------------------------------
>> Jan de Visser jdevisser@digitalfairway.com
>>
>>   Baruk Khazad! Khazad ai-menu!
>> --------------------------------------------------------------
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>