From: Yeb Havinga <yebhavinga@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: GiST index performance
Date: Fri, 19 Mar 2010 21:49:30 +0100
Message-ID: <4BA3E35A.1000905@gmail.com> (raw)
In-Reply-To: <4BA3DCA1.2010808@gmail.com>
References: <201002252342.o1PNgtG07127@momjian.us>
<alpine.DEB.2.00.1003151548180.1887@aragorn.flymine.org>
<4B9FAF4B.20907@gmail.com>
<4BA0A036.1070508@gmail.com>
<4BA3A6C6.80406@gmail.com>
<4BA3DCA1.2010808@gmail.com>
Yeb Havinga wrote:
>
> Since the gistpagesize is derived from the database blocksize, it
> might be wise to set the blocksize low for this case, I'm going to
> play with this a bit more.
Ok, one last mail before it turns into spam: with a 1KB database
blocksize, the query now runs in 30 seconds (original 70 on my machine,
shared buffers 240MB).
The per inner loop access time now 24 microsecs compared to on my
machine original 74 microsecs with 8KB size and 8 for the btree scan.
Not a bad speedup with such a simple parameter :-)
postgres=# EXPLAIN ANALYSE SELECT * FROM a, b WHERE a.a BETWEEN b.b AND
b.b + 2;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..4169159462.20 rows=111109777668 width=8)
(actual time=0.184..29540.355 rows=2999997 loops=1)
-> Seq Scan on b (cost=0.00..47037.62 rows=999962 width=4) (actual
time=0.024..1783.484 rows=1000000 loops=1)
-> Index Scan using a_a on a (cost=0.00..2224.78 rows=111114
width=4) (actual time=0.021..0.024 rows=3 loops=1000000)
Index Cond: ((a.a >= b.b) AND (a.a <= (b.b + 2)))
Total runtime: 30483.303 ms
(5 rows)
postgres=# select gist_stat('a_a');
gist_stat
-------------------------------------------
Number of levels: 5 +
Number of pages: 47618 +
Number of leaf pages: 45454 +
Number of tuples: 1047617 +
Number of invalid tuples: 0 +
Number of leaf tuples: 1000000 +
Total size of tuples: 21523756 bytes+
Total size of leaf tuples: 20545448 bytes+
Total size of index: 48760832 bytes+
(1 row)
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: yebhavinga@gmail.com, matthew@flymine.org, bruce@momjian.us
Subject: Re: GiST index performance
In-Reply-To: <4BA3E35A.1000905@gmail.com>
* 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