DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=M5X8dNeYLlZ+Fi7MjkazIK6gTgntPyjj5u0A99jkgb2MSgzWWU7ny4nVTKDjlIAp/R
	r/FKrV7OekQ6gGBqfzkA7JSX7eUXqqMzVczokwOhDEZgz/TyyN5jNiFCdNXi/a/Qihng
	EJW1F9OMjBNCPOiN7FSQLeV6erDZXWfowkgVE=
Message-ID: <4BA3E35A.1000905@gmail.com>
Date: Fri, 19 Mar 2010 21:49:30 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Matthew Wakeling <matthew@flymine.org>
CC: Bruce Momjian <bruce@momjian.us>, pgsql-performance@postgresql.org
Subject: Re: GiST index performance
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>
In-Reply-To: <4BA3DCA1.2010808@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)