From: Kenneth Marshall <ktm@rice.edu>
To: Yeb Havinga <yebhavinga@gmail.com>
Cc: 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 16:16:38 -0500
Message-ID: <20100319211638.GI1218@it.is.rice.edu> (raw)
In-Reply-To: <4BA3E35A.1000905@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>
<4BA3E35A.1000905@gmail.com>
Hi Yeb,
I have not looked at the gist code, but would it be possible to
make virtual pages that have a size that is 1/power-of-2 * blocksize.
Then the leaf node could be 1/8 or even 1/16 the size of the full
pagesize.
Regards,
Ken
On Fri, Mar 19, 2010 at 09:49:30PM +0100, Yeb Havinga wrote:
> 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)
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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: ktm@rice.edu, yebhavinga@gmail.com, matthew@flymine.org, bruce@momjian.us
Subject: Re: GiST index performance
In-Reply-To: <20100319211638.GI1218@it.is.rice.edu>
* 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