On 4/3/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Alex Deucher" <[EMAIL PROTECTED]> writes:
> Turning off bitmapscan ends up doing a sequential scan. Turning off
> both bitmapscan and seqscan results in a bitmap heap scan. It doesn't
> seem to want to use the index at all. Any ideas?
The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
we use bitmap OR'ing instead. There actually are repeated indexscans
hidden under the "= ANY" indexscan condition in 8.2, it's just that
the mechanism for detecting duplicate matches is different. AFAIK the
index access costs ought to be about the same either way, and the other
costs the same or better as what we did in 7.4. It's clear though that
8.2 is taking some kind of big hit in the index access in your case.
There's something very strange going on here.
You do have both lc_collate and lc_ctype set to C, right? What about
database encoding?
SHOW lc_collate ;
lc_collate
------------
C
(1 row)
SHOW lc_ctype ;
lc_ctype
----------
C
(1 row)
The encoding is UTF8, however I also built a SQL_ASCII version of the
DB to compare performance, but they both seem to perform about the
same.
Alex
SQL_ASCII:
EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in
('AB5679927','AB4974075','AB5066236','AB4598969','AB5009616','AB6409547','AB5593311','AB4975084','AB6604964','AB5637015','AB5135405','AB4501459','AB5605469','AB5603634','AB6000955','AB5718599','AB5328380','AB4846727');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=80.41..152.72 rows=18 width=157)
(actual time=157.210..283.140 rows=18 loops=1)
Recheck Cond: ((num)::text = ANY
(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
-> Bitmap Index Scan on t1_pkey (cost=0.00..80.41 rows=18
width=0) (actual time=140.419..140.419 rows=18 loops=1)
Index Cond: ((num)::text = ANY
(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
Total runtime: 283.214 ms
(5 rows)
UTF8:
EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in
('AB5679927','AB4974075','AB5066236','AB4598969','AB5009616','AB6409547','AB5593311','AB4975084','AB6604964','AB5637015','AB5135405','AB4501459','AB5605469','AB5603634','AB6000955','AB5718599','AB5328380','AB4846727');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=80.41..152.72 rows=18 width=159)
(actual time=126.194..126.559 rows=18 loops=1)
Recheck Cond: ((num)::text = ANY
(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
-> Bitmap Index Scan on t1_pkey (cost=0.00..80.41 rows=18
width=0) (actual time=126.155..126.155 rows=18 loops=1)
Index Cond: ((num)::text = ANY
(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
Total runtime: 126.661 ms
(5 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
