Thread: Slow query using simple equality operators

Slow query using simple equality operators

From

Benjamin Arai

Date:

23 April 2007, 22:38:17

Can anybody explain to me why this query is executing so slow?

=# explain select s_content,textdir from text_search where
path_id='1' AND tb_id='P2_TB00001';
                                            QUERY PLAN
------------------------------------------------------------------------
-------------------------
Bitmap Heap Scan on text_search  (cost=39861.94..59743.55 rows=5083
width=36)
    Recheck Cond: ((path_id = 1) AND (tb_id = 'P2_TB00001'::text))
    ->  BitmapAnd  (cost=39861.94..39861.94 rows=5083 width=0)
          ->  Bitmap Index Scan on idx_search_path_id
(cost=0.00..16546.09 rows=1016571 width=0)
                Index Cond: (path_id = 1)
          ->  Bitmap Index Scan on idx_search_tb_id
(cost=0.00..23315.60 rows=1016571 width=0)
                Index Cond: (tb_id = 'P2_TB00001'::text)
(7 rows)

Thanks in advance!

Benjamin

Re: Slow query using simple equality operators

From

Benjamin Arai

Date:

23 April 2007, 23:48:39

To follow up on my own email, by disabling BitmapScan in my
postgresql.conf the performance seems to be better. Is something
wrong with the query analyzer?

v=# explain select s_content,textdir from text_search where
path_id='1' AND tb_id='P2_TB00001';
                                            QUERY PLAN
------------------------------------------------------------------------
------------------------
Index Scan using idx_search_path_id on text_search
(cost=0.00..4081857.23 rows=5083 width=36)
    Index Cond: (path_id = 1)
    Filter: (tb_id = 'P2_TB00001'::text)
(3 rows)

Benjamin

On Apr 23, 2007, at 3:38 PM, Benjamin Arai wrote:

> Can anybody explain to me why this query is executing so slow?
>
> =# explain select s_content,textdir from text_search where
> path_id='1' AND tb_id='P2_TB00001';
>                                            QUERY PLAN
> ----------------------------------------------------------------------
> ---------------------------
> Bitmap Heap Scan on text_search  (cost=39861.94..59743.55 rows=5083
> width=36)
>    Recheck Cond: ((path_id = 1) AND (tb_id = 'P2_TB00001'::text))
>    ->  BitmapAnd  (cost=39861.94..39861.94 rows=5083 width=0)
>          ->  Bitmap Index Scan on idx_search_path_id
> (cost=0.00..16546.09 rows=1016571 width=0)
>                Index Cond: (path_id = 1)
>          ->  Bitmap Index Scan on idx_search_tb_id
> (cost=0.00..23315.60 rows=1016571 width=0)
>                Index Cond: (tb_id = 'P2_TB00001'::text)
> (7 rows)
>
> Thanks in advance!
>
> Benjamin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: Slow query using simple equality operators

From

Tom Lane

Date:

24 April 2007, 00:13:02

Benjamin Arai <benjamin@araisoft.com> writes:
> To follow up on my own email, by disabling BitmapScan in my
> postgresql.conf the performance seems to be better. Is something
> wrong with the query analyzer?

I just rewrote choose_bitmap_and() to fix some issues that might be
related to this; please try your query with 8.2.4 or 8.1.9 as the
case may be (and next time, mention what version you're using
right off the bat).

            regards, tom lane

Re: Slow query using simple equality operators

From

Benjamin Arai

Date:

24 April 2007, 00:45:51

Hi,

I upgraded to 8.2.4 but there was no significant change in performance. I did notice that hte query appears to be executed incorrectly. Specifically, it appears to perform each equality operation then perform a bitwise AND. I think it should instead be performing one of the equalities then use the results to perform the other. This would create a vastly smaller dataset for the second to work with.

I have pasted the EXPLAIN ANALYZE below to illustrate:

=# explain analyze select s_content,textdir from (SELECT * from text_search WHERE tb_id='P2_TB00001') AS a where path_id='4';

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083 width=36) (actual time=7418.651..7418.863 rows=52 loops=1)

Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text))

-> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual time=6706.928..6706.928 rows=0 loops=1)

-> Bitmap Index Scan on idx_search_path_id (cost=0.00..16546.34 rows=1016571 width=0) (actual time=6609.458..6609.458 rows=52777 loops=1)

Index Cond: (path_id = 4)

-> Bitmap Index Scan on idx_search_tb_id (cost=0.00..23315.85 rows=1016571 width=0) (actual time=96.903..96.903 rows=411341 loops=1)

Index Cond: (tb_id = 'P2_TB00001'::text)

Total runtime: 7419.128 ms

(8 rows)

Is there are way to force the "Bitmap Index Scan on idx_search_tb_id" to perform first then let "Bitmap Index Scan on idx_search_path_id" use the results?

Benjamin

On Apr 23, 2007, at 5:12 PM, Tom Lane wrote:

Benjamin Arai <benjamin@araisoft.com> writes:
To follow up on my own email, by disabling BitmapScan in my
postgresql.conf the performance seems to be better. Is something
wrong with the query analyzer?

I just rewrote choose_bitmap_and() to fix some issues that might be
related to this; please try your query with 8.2.4 or 8.1.9 as the
case may be (and next time, mention what version you're using
right off the bat).

regards, tom lane

Re: Slow query using simple equality operators

From

Alban Hertroys

Date:

24 April 2007, 08:12:44

Benjamin Arai wrote:
> Hi,
>
> I upgraded to 8.2.4 but there was no significant change in performance.
> I did notice that hte query appears to be executed incorrectly.

> I have pasted the EXPLAIN ANALYZE below to illustrate:
>
> =# explain analyze select s_content,textdir from (SELECT * from
> text_search WHERE tb_id='P2_TB00001') AS a where path_id='4';

What's wrong with a plain select * from text_search where
tb_id='P2_TB00001' and path_id=4; ?

You posted the explain output of something like that earlier, but that
was on an older pg 8 and without analyze.

>
> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------
>
> Bitmap Heap Scan on text_search  (cost=39864.98..59746.59 rows=5083
> width=36) (actual time=7418.651..7418.863 rows=52 loops=1)
>    Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text))
>    ->  BitmapAnd  (cost=39864.98..39864.98 rows=5083 width=0) (actual
> time=6706.928..6706.928 rows=0 loops=1)
>          ->  Bitmap Index Scan on idx_search_path_id
> (cost=0.00..16546.34 rows=1016571 width=0) (actual
> time=6609.458..6609.458 rows=52777 loops=1)

The row estimates are off by a factor 20 (~1M rows estimated, 50k rows
actual). Are you sure you analyze'd recently?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Slow query using simple equality operators

From

Benjamin Arai

Date:

25 April 2007, 04:25:21

Hi,

Apparently, the amount of free space on the partition makes a big
difference in performance.  I went from about 30% free space to about
5% and this triggered the performance issues.  As soon as freed up
the drive to about 30% free space again the performance issues went
away.

Benjamin

On Apr 24, 2007, at 1:12 AM, Alban Hertroys wrote:

> Benjamin Arai wrote:
>> Hi,
>>
>> I upgraded to 8.2.4 but there was no significant change in
>> performance.
>> I did notice that hte query appears to be executed incorrectly.
>
>> I have pasted the EXPLAIN ANALYZE below to illustrate:
>>
>> =# explain analyze select s_content,textdir from (SELECT * from
>> text_search WHERE tb_id='P2_TB00001') AS a where path_id='4';
>
> What's wrong with a plain select * from text_search where
> tb_id='P2_TB00001' and path_id=4; ?
>
> You posted the explain output of something like that earlier, but that
> was on an older pg 8 and without analyze.
>
>>
>> QUERY PLAN
>> ---------------------------------------------------------------------
>> ---------------------------------------------------------------------
>> -----------
>>
>> Bitmap Heap Scan on text_search  (cost=39864.98..59746.59 rows=5083
>> width=36) (actual time=7418.651..7418.863 rows=52 loops=1)
>>    Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text))
>>    ->  BitmapAnd  (cost=39864.98..39864.98 rows=5083 width=0) (actual
>> time=6706.928..6706.928 rows=0 loops=1)
>>          ->  Bitmap Index Scan on idx_search_path_id
>> (cost=0.00..16546.34 rows=1016571 width=0) (actual
>> time=6609.458..6609.458 rows=52777 loops=1)
>
> The row estimates are off by a factor 20 (~1M rows estimated, 50k rows
> actual). Are you sure you analyze'd recently?
>
> --
> Alban Hertroys
> alban@magproductions.nl
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
>    7500 AK Enschede
>
> // Integrate Your World //
>