From: Paul <paul@wayr.org>
To: pgsql-performance@postgresql.org
Subject: index & Bitmap Heap Scan
Date: Tue, 28 Aug 2007 10:49:09 +0200
Message-ID: <1188290949.29582.9.camel@thory> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: paul@wayr.org
Subject: Re: index & Bitmap Heap Scan
In-Reply-To: <1188290949.29582.9.camel@thory>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox