On Sun, Nov 2, 2008 at 5:36 PM, Hannes Eder <[EMAIL PROTECTED]> wrote:
> On Sun, Nov 2, 2008 at 10:49 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote:
>> Similarly, I
>> created a GUC to enable pruning, named bloom_pruning.
>
> I guess calls to bloom_filter_XXX should be surrounded by "if
> (bloom_pruning) ..." or a similar construct, i.e. make use of the GUC
> variable bloom_pruning in the rest of the code.
It's effective as-is for a preliminary patch. The GUC code is the
least of my worries.
> Can you provide some figures on the performance impact of the bloom filter?
It depends on the queries. I've been trying to find a good suite of
hash join tests... but not much luck.
CREATE TABLE t1 (id INTEGER PRIMARY KEY, x INTEGER);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, x INTEGER);
INSERT INTO t1 (SELECT ge, ge % 100 FROM generate_series(1, 1000000) ge);
INSERT INTO t2 (SELECT * FROM t1);
VACUUM ANALYZE;
SELECT COUNT(*)
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
SET bloom_pruning TO off;
EXPLAIN
SELECT COUNT(*)
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
SELECT COUNT(*)
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
EXPLAIN
SELECT *
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
SELECT *
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
SET bloom_pruning TO on;
\timing
SELECT COUNT(*)
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
EXPLAIN
SELECT *
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
SELECT *
FROM t1, t2
WHERE t1.id = t2.id
AND t1.x < 30
AND t2.x > 10;
\timing
-- Without Pruning
Time: 1142.843 ms
Time: 1567.355 ms
-- With Pruning
Time: 891.557 ms
Time: 1269.634 ms
--
Jonah H. Harris, Senior DBA
myYearbook.com
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
