Optimizing Bitmap Heap Scan.

Поиск
Список
Период
Сортировка
От niraj patel
Тема Optimizing Bitmap Heap Scan.
Дата
Msg-id 978609.36314.qm@web1211.biz.mail.gq1.yahoo.com
обсуждение исходный текст
Ответы Re: Optimizing Bitmap Heap Scan.  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Re: Optimizing Bitmap Heap Scan.  (Matthew Wakeling <matthew@flymine.org>)
Список pgsql-performance
Hi All,

I have to optimize following query :

SELECT r.TopFamilyID AS FamilyID,  FROM CMRules r
           WHERE r.WorkspaceID =18512 
            GROUP BY r.TopFamilyID ;

The explain plan is as follows :

 Group  (cost=509989.19..511518.30 rows=9 width=10) (actual time=1783.102..2362.587 rows=261 loops=1)
   ->  Sort  (cost=509989.19..510753.74 rows=305821 width=10) (actual time=1783.097..2121.378 rows=272211 loops=1)
         Sort Key: topfamilyid
         ->  Bitmap Heap Scan on cmrules r  (cost=14501.36..476896.34 rows=305821 width=10) (actual time=51.507..351.487 rows=272211 loops=1)
               Recheck Cond: (workspaceid = 18512::numeric)
               ->  Bitmap Index Scan on pk_ws_fea_fam_cmrules  (cost=0.00..14424.90 rows=305821 width=0) (actual time=48.097..48.097 rows=272211 loops=1)
                     Index Cond: (workspaceid = 18512::numeric)
 Total runtime: 2373.008 ms
(8 rows)
-----------------------------------------------------------------------------------------------------------------
\d CMRules gives follows indexes

Indexes:
    "pk_ws_fea_fam_cmrules" PRIMARY KEY, btree (workspaceid, featureid, topfamilyid, ruleenddate, gid)
    "idx_cmrules" btree (topfamilyid)
    "idx_gid_ws_cmrules" btree (gid, workspaceid)
-----------------------------------------------------------------------------------------------------------------
SELECT count(distinct r.TopFamilyID) FROM CMRules r  WHERE r.WorkspaceID =18512

Gives me 261 Rows

SELECT count(r.TopFamilyID) FROM CMRules r  WHERE r.WorkspaceID =18512  ;

Gives me 272 211 Rows

select count(*) from  cmrules;

Gives me 17 643 532 Rows


Please suggest me something to optimize this query

Thanks
Niraj Patel

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

Предыдущее
От: "Schmitz, David"
Дата:
Сообщение: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Следующее
От: Grzegorz Jaśkiewicz
Дата:
Сообщение: Re: Optimizing Bitmap Heap Scan.