Re: GiST index performance

От: Kenneth Marshall
Тема: Re: GiST index performance
Дата: ,
Msg-id: 20100319211638.GI1218@it.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Re: GiST index performance  (Yeb Havinga)
Ответы: Re: GiST index performance  (Yeb Havinga)
Список: pgsql-performance

Скрыть дерево обсуждения

GiST index performance  (Matthew Wakeling, )
 Re: GiST index performance  (Robert Haas, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Tom Lane, )
    Re: GiST index performance  (Matthew Wakeling, )
     Re: GiST index performance  (Tom Lane, )
      Re: GiST index performance  (Matthew Wakeling, )
       Re: GiST index performance  (Tom Lane, )
        Re: GiST index performance  (Matthew Wakeling, )
         Re: GiST index performance  (Matthew Wakeling, )
          Re: GiST index performance  (Matthew Wakeling, )
           Re: GiST index performance  (Tom Lane, )
           Re: GiST index performance  (Adam Gundy, )
        Re: GiST index performance  (Heikki Linnakangas, )
      Re: GiST index performance  (Greg Smith, )
       Re: GiST index performance  (Robert Haas, )
 Re: GiST index performance  (Bruce Momjian, )
  Re: GiST index performance  (Robert Haas, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Robert Haas, )
   Re: GiST index performance  (Yeb Havinga, )
    Re: GiST index performance  (Yeb Havinga, )
     Re: GiST index performance  (Yeb Havinga, )
      Re: GiST index performance  (Yeb Havinga, )
       Re: GiST index performance  (Yeb Havinga, )
        Re: GiST index performance  (Kenneth Marshall, )
         Re: GiST index performance  (Yeb Havinga, )
          Re: GiST index performance  (Matthew Wakeling, )
           Re: GiST index performance  (Yeb Havinga, )
            Re: GiST index performance  (Matthew Wakeling, )

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 ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: PG using index+filter instead only use index
От: Reydan Cankur
Дата:
Сообщение: pgbench installation