Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 2F16E9F9EBD for ; Tue, 28 Aug 2007 05:49:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 36757-10 for ; Tue, 28 Aug 2007 05:49:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp.wayr.org (wayr.org [88.191.18.57]) by postgresql.org (Postfix) with ESMTP id 0DDF89F9EC6 for ; Tue, 28 Aug 2007 05:49:12 -0300 (ADT) Received: from [192.168.22.6] (staff.syllage.fr [213.186.54.121]) by smtp.wayr.org (Postfix) with ESMTP id 2EEF7B74C for ; Tue, 28 Aug 2007 10:49:11 +0200 (CEST) Subject: index & Bitmap Heap Scan From: Paul To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="=-OpQfoB+zr5Xckygmo3qT" Date: Tue, 28 Aug 2007 10:49:09 +0200 Message-Id: <1188290949.29582.9.camel@thory> Mime-Version: 1.0 X-Mailer: Evolution 2.10.3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200708/335 X-Sequence-Number: 26465 --=-OpQfoB+zr5Xckygmo3qT Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Hello, I have a table (stats.tickets) with 2288965 rows (51 columns) and indexes like: ind_ti_stats_numero btree (tday, tmonth, tyear, r_cat, r_numero) ind_ti_stats_service btree (tday, tmonth, tyear, r_cat, r_service) ind_ti_stats_tmp_service btree (r_service, tyear, tmonth) ind_ti_stats_tmp_service2 btree (r_service, tyear, tmonth, r_cat) Now if i do : 1°)# explain analyze SELECT tday AS n, '' AS class, a.r_cat AS cat, COUNT(*) AS cnx, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE 1 END) AS p, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 1 ELSE 0 END) AS np, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE r_duree END) AS tps, ROUND(AVG(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE t_duree1 END),0) AS tmc FROM stats.tickets AS a WHERE a.r_numero='99084040' AND tyear = 2007 AND tmonth = 8 GROUP BY tyear, tmonth, tday, a.r_cat; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=45412.96..45412.99 rows=1 width=34) (actual time=649.944..650.178 rows=50 loops=1) -> Index Scan using ind_ti_stats_numero on tickets a (cost=0.00..45385.46 rows=1222 width=34) (actual time=15.697..642.570 rows=1043 loops=1) Index Cond: ((tmonth = 8) AND (tyear = 2007) AND ((r_numero)::text = '99084040'::text)) Total runtime: 650.342 ms (4 lignes) Temps : 652,234 ms 2°) # explain analyze SELECT tday AS n, '' AS class, a.r_cat AS cat, COUNT(*) AS cnx, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE 1 END) AS p, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 1 ELSE 0 END) AS np, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE r_duree END) AS tps, ROUND(AVG(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE t_duree1 END),0) AS tmc FROM stats.tickets AS a WHERE a.r_service=95 AND tyear = 2007 AND tmonth = 8 GROUP BY tyear, tmonth, tday, a.r_cat; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=193969.97..193970.88 rows=26 width=34) (actual time=20834.559..20834.694 rows=27 loops=1) -> Bitmap Heap Scan on tickets a (cost=3714.84..186913.32 rows=313629 width=34) (actual time=889.880..19028.315 rows=321395 loops=1) Recheck Cond: ((r_service = 95) AND (tyear = 2007) AND (tmonth = 8)) -> Bitmap Index Scan on ind_ti_stats_tmp_service (cost=0.00..3714.84 rows=313629 width=0) (actual time=836.181..836.181 rows=321395 loops=1) Index Cond: ((r_service = 95) AND (tyear = 2007) AND (tmonth = 8)) Total runtime: 20835.191 ms (6 lignes) Temps : 20838,798 ms \d stats.tickets [...] r_numero | character varying(17) | not null r_service | integer | not null default 0 [...] stats.tickets has 173351 relpages , 2.30996e+06 reltuples. Why in the first case, pgsql uses the "better" index and if i search r_service instead of r_numero pgsql does a "Bitmap Heap scan" first ? There ara too much rows in this table ? PS: sorry for my english, i'm french. -- Paul. --=-OpQfoB+zr5Xckygmo3qT Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Hello,


I have a table (stats.tickets) with  2288965 rows (51 columns) and indexes like:
ind_ti_stats_numero btree (tday, tmonth, tyear, r_cat, r_numero)
ind_ti_stats_service btree (tday, tmonth, tyear, r_cat, r_service)
ind_ti_stats_tmp_service btree (r_service, tyear, tmonth)
ind_ti_stats_tmp_service2 btree (r_service, tyear, tmonth, r_cat)


Now if i do :
1°)# explain analyze SELECT  tday AS n,  '' AS class, a.r_cat AS cat, COUNT(*) AS cnx, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE 1 END) AS p,  SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 1 ELSE 0 END) AS np,  SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE r_duree END) AS tps, ROUND(AVG(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE t_duree1 END),0) AS tmc FROM stats.tickets AS a  WHERE a.r_numero='99084040'  AND tyear = 2007 AND tmonth = 8  GROUP BY  tyear, tmonth, tday, a.r_cat;
                                                                    QUERY PLAN                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=45412.96..45412.99 rows=1 width=34) (actual time=649.944..650.178 rows=50 loops=1)
   ->  Index Scan using ind_ti_stats_numero on tickets a  (cost=0.00..45385.46 rows=1222 width=34) (actual time=15.697..642.570 rows=1043 loops=1)
         Index Cond: ((tmonth = 8) AND (tyear = 2007) AND ((r_numero)::text = '99084040'::text))
Total runtime: 650.342 ms
(4 lignes)

Temps : 652,234 ms



2°)
# explain analyze SELECT  tday AS n,  '' AS class, a.r_cat AS cat, COUNT(*) AS cnx, SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE 1 END) AS p,  SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 1 ELSE 0 END) AS np,  SUM(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE r_duree END) AS tps, ROUND(AVG(CASE WHEN t_duree1 < r_palier_min_con THEN 0 ELSE t_duree1 END),0) AS tmc FROM stats.tickets AS a  WHERE a.r_service=95  AND tyear = 2007 AND tmonth = 8  GROUP BY  tyear, tmonth, tday, a.r_cat;
                                                                       QUERY PLAN                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=193969.97..193970.88 rows=26 width=34) (actual time=20834.559..20834.694 rows=27 loops=1)
   ->  Bitmap Heap Scan on tickets a  (cost=3714.84..186913.32 rows=313629 width=34) (actual time=889.880..19028.315 rows=321395 loops=1)
         Recheck Cond: ((r_service = 95) AND (tyear = 2007) AND (tmonth = 8))
         ->  Bitmap Index Scan on ind_ti_stats_tmp_service  (cost=0.00..3714.84 rows=313629 width=0) (actual time=836.181..836.181 rows=321395 loops=1)
               Index Cond: ((r_service = 95) AND (tyear = 2007) AND (tmonth = 8))
Total runtime: 20835.191 ms
(6 lignes)

Temps : 20838,798 ms


\d stats.tickets
[...]
r_numero            | character varying(17)       | not null
r_service           | integer                     | not null default 0
[...]
stats.tickets has 173351 relpages , 2.30996e+06 reltuples.


Why in the first case, pgsql uses the "better" index and if i search r_service instead of r_numero pgsql does a "Bitmap Heap scan" first ?
There ara too much rows in this table ?




PS: sorry for my english, i'm french.

--
Paul. --=-OpQfoB+zr5Xckygmo3qT--