pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedForcing more agressive index scans for BITMAP AND
7+ messages / 3 participants
[nested] [flat]
* Forcing more agressive index scans for BITMAP AND
@ 2008-04-04 03:39 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ow Mun Heng @ 2008-04-04 03:39 UTC (permalink / raw)
To: pgsql-performance
just wondering if there's a special tweak i can do to force more usage
of indexes to do BITMAP ands?
I have a table like
A int
B int
C int
D int
E int
F int
g int
where A/B/C/D/E are indexes
There's ~20millions rows in the table.
Query are something like this.
select * from table
where A=X
and B = Y
and C = Z
and D = AA
and E = BB
the query plan will only pick 2 indexes to do the bitmap.
I'm not sure how to tweak the config for it to use more indexes.
Box is a celeron 1.7 w/ 768MB ram with shared buffers at 250MB and
effective cache size 350MB
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Forcing more agressive index scans for BITMAP AND
@ 2008-04-04 10:40 Matthew <matthew@flymine.org>
parent: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Matthew @ 2008-04-04 10:40 UTC (permalink / raw)
To: pgsql-performance
On Fri, 4 Apr 2008, Ow Mun Heng wrote:
> select * from table
> where A=X
> and B = Y
> and C = Z
> and D = AA
> and E = BB
This may not be the answer you're looking for, but if you create a
multi-coloumn index, it should be able to make your query run fast:
CREATE INDEX foo ON table (A, B, C, D, E);
It'll certainly be faster than building a bitmap for the contents of five
separate indexes.
Matthew
--
-. .-. .-. .-. .-. .-. .-. .-. .-. .-. .-. .-. .-.
||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||
|/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/
' `-' `-' `-' `-' `-' `-' `-' `-' `-' `-' `-' `-'
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Forcing more agressive index scans for BITMAP AND
@ 2008-04-04 11:02 PFC <lists@peufeu.com>
parent: Matthew <matthew@flymine.org>
0 siblings, 0 replies; 7+ messages in thread
From: PFC @ 2008-04-04 11:02 UTC (permalink / raw)
To: pgsql-performance
> On Fri, 4 Apr 2008, Ow Mun Heng wrote:
>> select * from table
>> where A=X
>> and B = Y
>> and C = Z
>> and D = AA
>> and E = BB
With that kind of WHERE condition, Postgres will use a Bitmap Index Scan
to combine your indices. If, however, postgres notices while looking at
the statistics gathered during ANALYZE, that for one of your columns, you
request a value that happens in a large percentage of the rows (like 20%),
and this value has a rather random distribution, Postgres will not bother
scanning the index, because it is very likely that all the pages would
contain a row satisfying your condition anyway, so the time taken to scan
this huge index and mark the bitmap would be lost because it would not
allow a better selectivity, since all the pages would get selected for
scan anyway.
I would guess that Postgres uses Bitmap Index Scan only on your columns
that have good selectivity (ie. lots of different values).
So :
If you use conditions on (a,b) or (a,b,c) or (a,b,c,d) etc, you will
benefit GREATLY from a multicolumn index on (a,b,c,d...).
However, even if postgres can use some clever tricks, a multicolumn index
on (a,b,c,d) will not be optimal for a condition on (b,c,d) for instance.
So, if you mostly use conditions on a left-anchored subset of
(a,b,c,d,e), the multicolumn index will be a great tool.
A multicolumn index on (a,b,c,d,e) is always slightly slower than an
index on (a) if you only use a condition on (a), but it is immensely
faster when you use a multicolumn condition.
Can you tell us more about what those columns mean and what you store in
them, how many distinct values, etc ?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Forcing more agressive index scans for BITMAP AND
@ 2008-04-07 08:15 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ow Mun Heng @ 2008-04-07 08:15 UTC (permalink / raw)
To: pgsql-performance
just wondering if there's a special tweak i can do to force more usage
of indexes to do BITMAP ands?
I have a table like
A int
B int
C int
D int
E int
F int
g int
where A/B/C/D/E are indexes
There's ~20millions rows in the table.
Query are something like this.
select * from table
where A=X
and B = Y
and C = Z
and D = AA
and E = BB
the query plan will only pick 2 indexes to do the bitmap.
I'm not sure how to tweak the config for it to use more indexes.
Box is a celeron 1.7 w/ 768MB ram with shared buffers at 250MB and
effective cache size 350MB
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Forcing more agressive index scans for BITMAP AND
@ 2008-04-07 10:50 Matthew <matthew@flymine.org>
parent: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Matthew @ 2008-04-07 10:50 UTC (permalink / raw)
To: pgsql-performance
On Mon, 7 Apr 2008, Ow Mun Heng wrote:
> just wondering if there's a special tweak i can do to force more usage
> of indexes to do BITMAP ands?
There's no need to post this again. You have already had a couple of
useful answers.
Matthew
--
All of this sounds mildly turgid and messy and confusing... but what the
heck. That's what programming's all about, really
-- Computer Science Lecturer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Forcing more agressive index scans for BITMAP AND
@ 2008-04-08 05:42 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
parent: Matthew <matthew@flymine.org>
0 siblings, 1 reply; 7+ messages in thread
From: Ow Mun Heng @ 2008-04-08 05:42 UTC (permalink / raw)
To: Matthew <matthew@flymine.org>; +Cc: pgsql-performance
On Mon, 2008-04-07 at 11:50 +0100, Matthew wrote:
> On Mon, 7 Apr 2008, Ow Mun Heng wrote:
> > just wondering if there's a special tweak i can do to force more usage
> > of indexes to do BITMAP ands?
>
> There's no need to post this again. You have already had a couple of
> useful answers.
Sorry about this. I didn't see any responses(and my own mail) in my
INBOX (I'm subscribed to the list and should be receiving all the
messages) and thus I thought that it didn't go through. I didn't check
the internet arhives as I do not have internet access at the workplace.
I saw the answers from the list at home though and I'm trying to answer
those questions below.
To answer (based on what I see in pgadmin)
index A = 378 distinct values
index B = 235
index C = 53
index D = 32
index E = 1305
index F = 246993 (This is timestamp w/o timezone)
(note that this is just 1 table and there are no joins whatsoever.)
I moved from multicolumn indexes to individual indexes because the
queries does not always utilise the same few indexes, some users would
use
eg: index F, A, B or D,A,E or any other combination.
with regard to the fact that perhaps a sec scan is much IO efficient,
this is true when using index F (timestamp) of > 2 weeks interval, then
it will ignore the other indexes to be searched but do a filter.
"Bitmap Heap Scan on dtt (cost=25109.93..30213.85 rows=1 width=264)"
" Recheck Cond: (((A)::text = 'H3'::text) AND (F >= '2008-04-01 00:00:00'::timestamp without time zone) AND (F <= '2008-04-08 00:00:00'::timestamp without time zone))"
" Filter: (((B)::text = ANY (('{P000,000}'::character varying[])::text[])) AND ((C)::text ~~ 'F8.M.Y%'::text))"
" -> BitmapAnd (cost=25109.93..25109.93 rows=1299 width=0)"
" -> Bitmap Index Scan on idx_dtt_A (cost=0.00..986.12 rows=47069 width=0)"
" Index Cond: ((A)::text = 'H3'::text)"
" -> Bitmap Index Scan on idx_dtt_date (cost=0.00..24123.56 rows=1007422 width=0)"
" Index Cond: ((F >= '2008-04-01 00:00:00'::timestamp without time zone) AND (F <= '2008-04-08 00:00:00'::timestamp without time zone))"
Changing the date to query from 3/10 to 4/8
"Bitmap Heap Scan on dtt (cost=47624.67..59045.32 rows=1 width=264)"
" Recheck Cond: (((A)::text = 'H3'::text) AND ((B)::text = 'MD'::text))"
" Filter: ((F >= '2008-03-10 00:00:00'::timestamp without time zone) AND (F <= '2008-04-08 00:00:00'::timestamp without time zone) AND ((B)::text = ANY (('{P000,000}'::character varying[])::text[])) AND ((C)::text ~~ 'F8.M.Y%'::text))"
" -> BitmapAnd (cost=47624.67..47624.67 rows=2944 width=0)"
" -> Bitmap Index Scan on idx_d_dtt (cost=0.00..986.13 rows=47070 width=0)"
" Index Cond: ((A)::text = 'H3'::text)"
" -> Bitmap Index Scan on idx_dtt_B (cost=0.00..46638.29 rows=2283910 width=0)"
" Index Cond: ((B)::text = 'MD'::text)"
I've seen many explains on my tables and IIRC never seen one in this it will use more than 2 indexes to do the query.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Forcing more agressive index scans for BITMAP AND
@ 2008-04-08 11:02 Matthew <matthew@flymine.org>
parent: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 0 replies; 7+ messages in thread
From: Matthew @ 2008-04-08 11:02 UTC (permalink / raw)
To: pgsql-performance
On Tue, 8 Apr 2008, Ow Mun Heng wrote:
> I moved from multicolumn indexes to individual indexes because the
> queries does not always utilise the same few indexes, some users would
> use
>
> eg: index F, A, B or D,A,E or any other combination.
Yes, that does make it more tricky, but it still may be best to use
multicolumn indexes. You would just need to create an index for each of
the combinations that you are likely to use.
Matthew
--
"To err is human; to really louse things up requires root
privileges." -- Alexander Pope, slightly paraphrased
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-04-08 11:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-04 03:39 Forcing more agressive index scans for BITMAP AND Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2008-04-04 10:40 ` Matthew <matthew@flymine.org>
2008-04-04 11:02 ` PFC <lists@peufeu.com>
2008-04-07 08:15 Forcing more agressive index scans for BITMAP AND Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2008-04-07 10:50 ` Matthew <matthew@flymine.org>
2008-04-08 05:42 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2008-04-08 11:02 ` Matthew <matthew@flymine.org>
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