Re: Slow query using simple equality operators

Поиск
Список
Период
Сортировка
От Benjamin Arai
Тема Re: Slow query using simple equality operators
Дата
Msg-id 31720C0B-9013-40CF-8C8F-BF2E2EB55A71@araisoft.com
обсуждение исходный текст
Ответ на Re: Slow query using simple equality operators  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Slow query using simple equality operators
Список pgsql-general
Дерево обсуждения
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


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