index & Bitmap Heap Scan - Mailing list pgsql-performance

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.