pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: GiST index performance
17+ messages / 5 participants
[nested] [flat]
* Re: GiST index performance
@ 2010-02-25 23:42 Bruce Momjian <bruce@momjian.us>
2010-03-02 16:21 ` Re: GiST index performance Robert Haas <robertmhaas@gmail.com>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
0 siblings, 2 replies; 17+ messages in thread
From: Bruce Momjian @ 2010-02-25 23:42 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Was there every any conclusion on this issue?
---------------------------------------------------------------------------
Matthew Wakeling wrote:
>
> Revisiting the thread a month back or so, I'm still investigating
> performance problems with GiST indexes in Postgres.
>
> Looking at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items I'd
> like to clarify the contrib/seg issue. Contrib/seg is vulnerable to
> pathological behaviour which is fixed by my second patch, which can be
> viewed as complete. Contrib/cube, being multi-dimensional, is not affected
> to any significant degree, so should not need alteration.
>
> A second quite distinct issue is the general performance of GiST indexes
> which is also mentioned in the old thread linked from Open Items. For
> that, we have a test case at
> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php for
> btree_gist indexes. I have a similar example with the bioseg GiST index. I
> have completely reimplemented the same algorithms in Java for algorithm
> investigation and instrumentation purposes, and it runs about a hundred
> times faster than in Postgres. I think this is a problem, and I'm willing
> to do some investigation to try and solve it.
>
> Do you have a recommendation for how to go about profiling Postgres, what
> profiler to use, etc? I'm running on Debian Linux x86_64.
>
> Matthew
>
> --
> Jadzia: Don't forget the 34th rule of acquisition: Peace is good for business.
> Quark: That's the 35th.
> Jadzia: Oh yes, that's right. What's the 34th again?
> Quark: War is good for business. It's easy to get them mixed up.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
@ 2010-03-02 16:21 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Robert Haas @ 2010-03-02 16:21 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Thu, Feb 25, 2010 at 6:42 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Was there every any conclusion on this issue?
I don't think so.
...Robert
> ---------------------------------------------------------------------------
>
> Matthew Wakeling wrote:
>>
>> Revisiting the thread a month back or so, I'm still investigating
>> performance problems with GiST indexes in Postgres.
>>
>> Looking at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items I'd
>> like to clarify the contrib/seg issue. Contrib/seg is vulnerable to
>> pathological behaviour which is fixed by my second patch, which can be
>> viewed as complete. Contrib/cube, being multi-dimensional, is not affected
>> to any significant degree, so should not need alteration.
>>
>> A second quite distinct issue is the general performance of GiST indexes
>> which is also mentioned in the old thread linked from Open Items. For
>> that, we have a test case at
>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php for
>> btree_gist indexes. I have a similar example with the bioseg GiST index. I
>> have completely reimplemented the same algorithms in Java for algorithm
>> investigation and instrumentation purposes, and it runs about a hundred
>> times faster than in Postgres. I think this is a problem, and I'm willing
>> to do some investigation to try and solve it.
>>
>> Do you have a recommendation for how to go about profiling Postgres, what
>> profiler to use, etc? I'm running on Debian Linux x86_64.
>>
>> Matthew
>>
>> --
>> Jadzia: Don't forget the 34th rule of acquisition: Peace is good for business.
>> Quark: That's the 35th.
>> Jadzia: Oh yes, that's right. What's the 34th again?
>> Quark: War is good for business. It's easy to get them mixed up.
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
> PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
@ 2010-03-15 15:58 ` Matthew Wakeling <matthew@flymine.org>
2010-03-16 01:58 ` Re: GiST index performance Robert Haas <robertmhaas@gmail.com>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
1 sibling, 2 replies; 17+ messages in thread
From: Matthew Wakeling @ 2010-03-15 15:58 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-performance
On Thu, 25 Feb 2010, Bruce Momjian wrote:
> Was there every any conclusion on this issue?
Not really. Comments inline:
> Matthew Wakeling wrote:
>> Revisiting the thread a month back or so, I'm still investigating
>> performance problems with GiST indexes in Postgres.
>>
>> Looking at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items I'd
>> like to clarify the contrib/seg issue. Contrib/seg is vulnerable to
>> pathological behaviour which is fixed by my second patch, which can be
>> viewed as complete. Contrib/cube, being multi-dimensional, is not affected
>> to any significant degree, so should not need alteration.
This issue is addressed by my patch, which AFAIK noone has reviewed.
However, that patch was derived from a patch that I applied to bioseg,
which is itself a derivative of seg. This patch works very well indeed,
and gave an approximate 100 times speed improvement in the one test I ran.
So you could say that the sister patch of the one I submitted is tried and
tested in production.
>> A second quite distinct issue is the general performance of GiST indexes
>> which is also mentioned in the old thread linked from Open Items. For
>> that, we have a test case at
>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php for
>> btree_gist indexes. I have a similar example with the bioseg GiST index. I
>> have completely reimplemented the same algorithms in Java for algorithm
>> investigation and instrumentation purposes, and it runs about a hundred
>> times faster than in Postgres. I think this is a problem, and I'm willing
>> to do some investigation to try and solve it.
I have not made any progress on this issue. I think Oleg and Teodor would
be better placed working it out. All I can say is that I implemented the
exact same indexing algorithm in Java, and it performed 100 times faster
than Postgres. Now, Postgres has to do a lot of additional work, like
mapping the index onto disc, locking pages, and abstracting to plugin user
functions, so I would expect some difference - I'm not sure 100 times is
reasonable though. I tried to do some profiling, but couldn't see any one
section of code that was taking too much time. Not sure what I can further
do.
Matthew
--
Some people, when confronted with a problem, think "I know, I'll use regular
expressions." Now they have two problems. -- Jamie Zawinski
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
@ 2010-03-16 01:58 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Robert Haas @ 2010-03-16 01:58 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
On Mon, Mar 15, 2010 at 11:58 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Thu, 25 Feb 2010, Bruce Momjian wrote:
>>
>> Was there every any conclusion on this issue?
>
> Not really. Comments inline:
>
>> Matthew Wakeling wrote:
>>>
>>> Revisiting the thread a month back or so, I'm still investigating
>>> performance problems with GiST indexes in Postgres.
>>>
>>> Looking at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items I'd
>>> like to clarify the contrib/seg issue. Contrib/seg is vulnerable to
>>> pathological behaviour which is fixed by my second patch, which can be
>>> viewed as complete. Contrib/cube, being multi-dimensional, is not
>>> affected
>>> to any significant degree, so should not need alteration.
>
> This issue is addressed by my patch, which AFAIK noone has reviewed.
> However, that patch was derived from a patch that I applied to bioseg, which
> is itself a derivative of seg. This patch works very well indeed, and gave
> an approximate 100 times speed improvement in the one test I ran.
>
> So you could say that the sister patch of the one I submitted is tried and
> tested in production.
We rely fairly heavily on the commitfest app to track which patches
need review; perhaps it would be good to add it here.
https://commitfest.postgresql.org/action/commitfest_view/open
I seem to recall thinking that this patch wasn't ready to apply for
some reason, but I'm not sure why I thought that.
...Robert
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
@ 2010-03-16 16:18 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-16 16:18 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
Matthew Wakeling wrote:
>> Matthew Wakeling wrote:
>>> A second quite distinct issue is the general performance of GiST
>>> indexes
>>> which is also mentioned in the old thread linked from Open Items. For
>>> that, we have a test case at
>>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
>>> for
>>> btree_gist indexes. I have a similar example with the bioseg GiST
>>> index. I
>>> have completely reimplemented the same algorithms in Java for algorithm
>>> investigation and instrumentation purposes, and it runs about a hundred
>>> times faster than in Postgres. I think this is a problem, and I'm
>>> willing
>>> to do some investigation to try and solve it.
> I have not made any progress on this issue. I think Oleg and Teodor
> would be better placed working it out. All I can say is that I
> implemented the exact same indexing algorithm in Java, and it
> performed 100 times faster than Postgres. Now, Postgres has to do a
> lot of additional work, like mapping the index onto disc, locking
> pages, and abstracting to plugin user functions, so I would expect
> some difference - I'm not sure 100 times is reasonable though. I tried
> to do some profiling, but couldn't see any one section of code that
> was taking too much time. Not sure what I can further do.
Hello Mathew and list,
A lot of time spent in gistget.c code and a lot of functioncall5's to
the gist's consistent function which is out of sight for gprof.
Something different but related since also gist: we noticed before that
gist indexes that use a compressed form for index entries suffer from
repeated compress calls on query operands (see
http://archives.postgresql.org/pgsql-hackers/2009-05/msg00078.php).
The btree_gist int4 compress function calls the generic
gbt_num_compress, which does a palloc. Maybe this palloc is allso hit al
lot when scanning the index, because the constants that are queries with
are repeatedly compressed and palloced.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-17 09:26 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-17 09:26 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
Yeb Havinga wrote:
> Matthew Wakeling wrote:
>>> Matthew Wakeling wrote:
>>>> A second quite distinct issue is the general performance of GiST
>>>> indexes
>>>> which is also mentioned in the old thread linked from Open Items. For
>>>> that, we have a test case at
>>>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
>>>> for
>>>> btree_gist indexes. I have a similar example with the bioseg GiST
>>>> index. I
>>>> have completely reimplemented the same algorithms in Java for
>>>> algorithm
>>>> investigation and instrumentation purposes, and it runs about a
>>>> hundred
>>>> times faster than in Postgres. I think this is a problem, and I'm
>>>> willing
>>>> to do some investigation to try and solve it.
>> I have not made any progress on this issue. I think Oleg and Teodor
>> would be better placed working it out. All I can say is that I
>> implemented the exact same indexing algorithm in Java, and it
>> performed 100 times faster than Postgres. Now, Postgres has to do a
>> lot of additional work, like mapping the index onto disc, locking
>> pages, and abstracting to plugin user functions, so I would expect
>> some difference - I'm not sure 100 times is reasonable though. I
>> tried to do some profiling, but couldn't see any one section of code
>> that was taking too much time. Not sure what I can further do.
> Hello Mathew and list,
>
> A lot of time spent in gistget.c code and a lot of functioncall5's to
> the gist's consistent function which is out of sight for gprof.
> Something different but related since also gist: we noticed before
> that gist indexes that use a compressed form for index entries suffer
> from repeated compress calls on query operands (see
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00078.php).
>
> The btree_gist int4 compress function calls the generic
> gbt_num_compress, which does a palloc. Maybe this palloc is allso hit
> al lot when scanning the index, because the constants that are queries
> with are repeatedly compressed and palloced.
Looked in the code a bit more - only the index nodes are compressed at
index creation, the consistent functions does not compress queries, so
not pallocs there. However when running Mathews example from
http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
with the gist index, the coverage shows in gistget.c: 1000000 palloc0 's
of gistsearchstack at line 152 and 2010982 palloc's also of the
gistsearchstack on line 342. Two pfrees are also hit a lot: line 195:
1010926 of a stackentry and line 293: 200056 times. My $0.02 cents is
that the pain is here. My knowledge of gistget or the other sources in
access/gist is zero, but couldn't it be possible to determine the
maximum needed size of the stack and then allocate it at once and use a
pop/push kind off api?
regards,
Yeb Havinga
>
> regards,
> Yeb Havinga
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-19 16:31 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-19 16:31 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
Yeb Havinga wrote:
> Yeb Havinga wrote:
>> Matthew Wakeling wrote:
>>>> Matthew Wakeling wrote:
>>>>> A second quite distinct issue is the general performance of GiST
>>>>> indexes
>>>>> which is also mentioned in the old thread linked from Open Items. For
>>>>> that, we have a test case at
>>>>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
>>>>> for
>>>>> btree_gist indexes. I have a similar example with the bioseg GiST
>>>>> index. I
>>>>> have completely reimplemented the same algorithms in Java for
>>>>> algorithm
>>>>> investigation and instrumentation purposes, and it runs about a
>>>>> hundred
>>>>> times faster than in Postgres. I think this is a problem, and I'm
>>>>> willing
>>>>> to do some investigation to try and solve it.
>>> I have not made any progress on this issue. I think Oleg and Teodor
>>> would be better placed working it out. All I can say is that I
>>> implemented the exact same indexing algorithm in Java, and it
>>> performed 100 times faster than Postgres. Now, Postgres has to do a
>>> lot of additional work, like mapping the index onto disc, locking
>>> pages, and abstracting to plugin user functions, so I would expect
>>> some difference - I'm not sure 100 times is reasonable though. I
>>> tried to do some profiling, but couldn't see any one section of code
>>> that was taking too much time. Not sure what I can further do.
> Looked in the code a bit more - only the index nodes are compressed at
> index creation, the consistent functions does not compress queries, so
> not pallocs there. However when running Mathews example from
> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
> with the gist index, the coverage shows in gistget.c: 1000000 palloc0
> 's of gistsearchstack at line 152 and 2010982 palloc's also of the
> gistsearchstack on line 342. Two pfrees are also hit a lot: line 195:
> 1010926 of a stackentry and line 293: 200056 times. My $0.02 cents is
> that the pain is here. My knowledge of gistget or the other sources in
> access/gist is zero, but couldn't it be possible to determine the
> maximum needed size of the stack and then allocate it at once and use
> a pop/push kind off api?
Waisted some time today on a ghost chase... I though that removing the
millions of pallocs would help, so I wrote an alternative of the
gistsearchstack-stack to find out that it was not the index scanning
itself that caused milltions of pallocs, but the scan being in the inner
loop that was called 1000000 times. The actual scanning time was not
changed significantly.
The actual scanning time in my vm is for btree (actual
time=0.006..0.008) and gist (actual time=0.071..0.074). An error in my
searchstack alternative caused pages to be scanned twice, returing twice
the amount of rows (6 instead of 3 each time). This resulted in a
likewise increase of ms (actual time=0.075..0.150). Somewhere I hit
something that causes ~= 0.070 ms twice. For a single index scan,
0.070ms startup time for gist vs 0.006 for btree doesn't seem like a big
problem, but yeah when calling it a million times...
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-19 20:20 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-19 20:20 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
Yeb Havinga wrote:
>>>>> Matthew Wakeling wrote:
>>>>>> A second quite distinct issue is the general performance of GiST
>>>>>> indexes
>>>>>> which is also mentioned in the old thread linked from Open Items.
>>>>>> For
>>>>>> that, we have a test case at
>>>>>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php
>>>>>> for
>>>>>> btree_gist indexes. I have a similar example with the bioseg GiST
>>>>>> index. I
>>>>>> have completely reimplemented the same algorithms in Java for
>>>>>> algorithm
>>>>>> investigation and instrumentation purposes, and it runs about a
>>>>>> hundred
>>>>>> times faster than in Postgres. I think this is a problem, and I'm
>>>>>> willing
>>>>>> to do some investigation to try and solve it.
More gist performance..
Some background: I've been involved in developing several datatypes that
make use of gist indexes (we just published a paper on it, see
http://arxiv.org/abs/1003.3370), that's the main reason I'm very much
interested in possible improvements in gist index speed. One of the
datatypes was 'very badly' indexable: non leaf pages were getting very
general keys, so in the end we could see from the scanning times
compared to sequential scans that the whole index was being scanned. One
thing I remember was the idea that somehow it would be nice if the dept
of the gist tree could be fiddled with: in that case keys of non leaf
nodes would be more specific again. In the original Guttman R-tree paper
there was mention of a parameter that determined the size of entries in
nodes and thereby indirectly the depth of the tree. I missed that in the
PostgreSQL gist api.
One thing Gist scanning does very often is key comparisons. So another
approach is to try to limit those and again this might be possible by
increasing the depth / decrease number of entries per page. I just did a
test where in gistfitpage the gistpagesize was divided by 5 and
something similar in gistnospace.
Scantime before adjustment: about 70 seconds.
After adjustment: 45 seconds.
With gist_stat from the gevel package confirmed that the depth was now 4
(original 3). Drawback: bigger index size because pages are not filled
completely anymore.
The explain shows (actual time=0.030..0.032) for the inner loop times,
which is less then half of the original scan time.
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.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-19 20:49 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Re: GiST index performance Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-19 20:49 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-19 21:16 ` Kenneth Marshall <ktm@rice.edu>
2010-03-20 16:16 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Kenneth Marshall @ 2010-03-19 21:16 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; Bruce Momjian <bruce@momjian.us>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Re: GiST index performance Kenneth Marshall <ktm@rice.edu>
@ 2010-03-20 16:16 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 13:29 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-20 16:16 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Matthew Wakeling <matthew@flymine.org>; Bruce Momjian <bruce@momjian.us>; pgsql-performance
On Fri, Mar 19, 2010 at 10:16 PM, Kenneth Marshall <ktm@rice.edu> wrote:
> 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.
>
Hello Ken,
The gist virtual pages would then match more the original blocksizes that
were used in Guttman's R-tree paper (first google result, then figure 4.5).
Since the nature/characteristics of the underlying datatypes and keys is not
changed, it might be that with the disk pages getting larger, gist indexing
has therefore become unexpectedly inefficient.
But I am also not really into the core-gist code, but do have a motivation
to dive into it (more than 200% performance increase in Mathew's test case).
However I'd like to verify for community support before working on it.
Maybe Theodor or Oleg could say something about how easy or hard it is to
do?
regards,
Yeb Havinga
>
> 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
> >
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Re: GiST index performance Kenneth Marshall <ktm@rice.edu>
2010-03-20 16:16 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-22 13:29 ` Matthew Wakeling <matthew@flymine.org>
2010-03-22 14:02 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Matthew Wakeling @ 2010-03-22 13:29 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: Kenneth Marshall <ktm@rice.edu>; Bruce Momjian <bruce@momjian.us>; pgsql-performance
On Sat, 20 Mar 2010, Yeb Havinga wrote:
> The gist virtual pages would then match more the original blocksizes that
> were used in Guttman's R-tree paper (first google result, then figure 4.5).
> Since the nature/characteristics of the underlying datatypes and keys is not
> changed, it might be that with the disk pages getting larger, gist indexing
> has therefore become unexpectedly inefficient.
Yes, that is certainly a factor. For example, the page size for bioseg
which we use here is 130 entries, which is very excessive, and doesn't
allow very deep trees. On the other hand, it means that a single disc seek
performs quite a lot of work.
> But I am also not really into the core-gist code, but do have a motivation
> to dive into it (more than 200% performance increase in Mathew's test case).
> However I'd like to verify for community support before working on it.
I'd also love to dive into the core gist code, but am rather daunted by
it. I believe that there is something there that is taking more time than
I can account for. The indexing algorithm itself is good.
Matthew
--
"The problem with defending the purity of the English language is that
English is about as pure as a cribhouse whore. We don't just borrow words;
on occasion, English has pursued other languages down alleyways to beat
them unconscious and rifle their pockets for new vocabulary." - James Nicoll
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Re: GiST index performance Kenneth Marshall <ktm@rice.edu>
2010-03-20 16:16 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 13:29 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
@ 2010-03-22 14:02 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 14:23 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 17+ messages in thread
From: Yeb Havinga @ 2010-03-22 14:02 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Kenneth Marshall <ktm@rice.edu>; Bruce Momjian <bruce@momjian.us>; pgsql-performance
Matthew Wakeling wrote:
> On Sat, 20 Mar 2010, Yeb Havinga wrote:
>> The gist virtual pages would then match more the original blocksizes
>> that
>> were used in Guttman's R-tree paper (first google result, then figure
>> 4.5).
>> Since the nature/characteristics of the underlying datatypes and keys
>> is not
>> changed, it might be that with the disk pages getting larger, gist
>> indexing
>> has therefore become unexpectedly inefficient.
>
> Yes, that is certainly a factor. For example, the page size for bioseg
> which we use here is 130 entries, which is very excessive, and doesn't
> allow very deep trees. On the other hand, it means that a single disc
> seek performs quite a lot of work.
Yeah, I only did in-memory fitting tests and wondered about increased
io's. However I bet that even for bigger than ram db's, the benefit of
having to fan out to less pages still outweighs the over-general non
leaf nodes and might still result in less disk io's. I redid some
earlier benchmarking with other datatypes with a 1kB block size and also
multicolumn gist and the multicolumn variant had an ever greater benefit
than the single column indexes, both equality and range scans. (Like
execution times down to 20% of original). If gist is important to you, I
really recommend doing a test with 1kB blocks.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-15 15:58 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-16 16:18 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Re: GiST index performance Kenneth Marshall <ktm@rice.edu>
2010-03-20 16:16 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 13:29 ` Re: GiST index performance Matthew Wakeling <matthew@flymine.org>
2010-03-22 14:02 ` Re: GiST index performance Yeb Havinga <yebhavinga@gmail.com>
@ 2010-03-22 14:23 ` Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 17+ messages in thread
From: Matthew Wakeling @ 2010-03-22 14:23 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: Kenneth Marshall <ktm@rice.edu>; Bruce Momjian <bruce@momjian.us>; pgsql-performance
On Mon, 22 Mar 2010, Yeb Havinga wrote:
>> Yes, that is certainly a factor. For example, the page size for bioseg
>> which we use here is 130 entries, which is very excessive, and doesn't
>> allow very deep trees. On the other hand, it means that a single disc seek
>> performs quite a lot of work.
> Yeah, I only did in-memory fitting tests and wondered about increased io's.
> However I bet that even for bigger than ram db's, the benefit of having to
> fan out to less pages still outweighs the over-general non leaf nodes and
> might still result in less disk io's. I redid some earlier benchmarking with
> other datatypes with a 1kB block size and also multicolumn gist and the
> multicolumn variant had an ever greater benefit than the single column
> indexes, both equality and range scans. (Like execution times down to 20% of
> original). If gist is important to you, I really recommend doing a test with
> 1kB blocks.
Purely from a disc seek count point of view, assuming an infinite CPU
speed and infinite disc transfer rate, the larger the index pages the
better. The number of seeks per fetch will be equivalent to the depth of
the tree.
If you take disc transfer rate into account, the break-even point is when
you spend an equal time transferring as seeking, which places the page
size around 500kB on a modern disc, assuming RAID stripe alignment doesn't
make that into two seeks instead of one.
However, for efficient CPU usage, the ideal page size for a tree index is
much smaller - between two and ten entries, depending on the type of the
data.
There may be some mileage in reorganising indexes into a two-level system.
That is, have an index format where the page size is 512kB or similar, but
each page is internally a CPU-efficient tree itself.
However, this is beyond the scope of the problem of speeding up gist.
Matthew
--
If you let your happiness depend upon how somebody else feels about you,
now you have to control how somebody else feels about you. -- Abraham Hicks
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
@ 2010-02-25 23:44 Bruce Momjian <bruce@momjian.us>
2010-03-02 16:23 ` Re: GiST index performance Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2010-02-25 23:44 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; Tom Lane <tgl@sss.pgh.pa.us>; Teodor Sigaev <teodor@sigaev.ru>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
Was this corrected? I don't see any commits to seg.c.
---------------------------------------------------------------------------
Matthew Wakeling wrote:
> On Thu, 7 May 2009, Oleg Bartunov wrote:
> > Did you try Guttman quadratic split algorithm ? We also found linear
> > split algorithm for Rtree.
>
> The existing (bugfixed) seg split algorithm is the Guttman quadratic split
> algorithm. Guttman did all his work on two-dimensional and above data,
> dismissing one-dimensional data as being handled adequately by B-trees,
> which is not true for segment overlaps. It turns out that the algorithm
> has a weakness with certain types of data, and one-dimensional data is
> almost certain to exercise that weakness. The greater the number of
> dimensions, the less the weakness is exercised.
>
> The problem is that the algorithm does not calculate a split pivot.
> Instead it finds two suitable entries, and adds the remaining entries to
> those two in turn. This can lead to the majority of the entries being
> added to just one side. In fact, I saw lots of cases where 367 entries
> were being split into two pages of 366 and one entry.
>
> Guttman's linear split algorithm has the same weakness.
>
> >> One thing I am seeing is a really big difference in performance between
> >> Postgres/GiST and a Java implementation I have written, using the same
> >> algorithms. Postgres takes three minutes to perform a set of index lookups
> >> while java takes six seconds. The old version of bioseg took an hour. I
> >> can't see anything in the GiST support code that could account for this.
> >
> > is the number of index lookups different, or just index lookup time is very
> > big ?
>
> Same number of index lookups. Same algorithms. I have a set of 681879
> segments, and I load them all into the index. I then query the index for
> overlaps for each one in turn. For some reason, GiST lookups seem to be
> slow, even if they are using a good algorithm. I have seen that problem
> with btree_gist on integers too. I can't see any reason for this is the
> GiST code - it all seems pretty tight to me. We probably need to do some
> profiling.
>
> Matthew
>
> --
> I suppose some of you have done a Continuous Maths course. Yes? Continuous
> Maths? <menacing stares from audience> Whoah, it was like that, was it!
> -- Computer Science Lecturer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:44 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
@ 2010-03-02 16:23 ` Robert Haas <robertmhaas@gmail.com>
2010-03-03 01:13 ` Re: GiST index performance Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Robert Haas @ 2010-03-02 16:23 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; Oleg Bartunov <oleg@sai.msu.su>; Tom Lane <tgl@sss.pgh.pa.us>; Teodor Sigaev <teodor@sigaev.ru>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
On Thu, Feb 25, 2010 at 6:44 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Was this corrected? I don't see any commits to seg.c.
I don't think this was ever reviewed.
It seems like a good patch but I'd be skeptical of committing it now
unless someone has the time to review it carefully. If not, let's add
it to the next CF so we don't lose it again.
...Robert
>
> ---------------------------------------------------------------------------
>
> Matthew Wakeling wrote:
>> On Thu, 7 May 2009, Oleg Bartunov wrote:
>> > Did you try Guttman quadratic split algorithm ? We also found linear
>> > split algorithm for Rtree.
>>
>> The existing (bugfixed) seg split algorithm is the Guttman quadratic split
>> algorithm. Guttman did all his work on two-dimensional and above data,
>> dismissing one-dimensional data as being handled adequately by B-trees,
>> which is not true for segment overlaps. It turns out that the algorithm
>> has a weakness with certain types of data, and one-dimensional data is
>> almost certain to exercise that weakness. The greater the number of
>> dimensions, the less the weakness is exercised.
>>
>> The problem is that the algorithm does not calculate a split pivot.
>> Instead it finds two suitable entries, and adds the remaining entries to
>> those two in turn. This can lead to the majority of the entries being
>> added to just one side. In fact, I saw lots of cases where 367 entries
>> were being split into two pages of 366 and one entry.
>>
>> Guttman's linear split algorithm has the same weakness.
>>
>> >> One thing I am seeing is a really big difference in performance between
>> >> Postgres/GiST and a Java implementation I have written, using the same
>> >> algorithms. Postgres takes three minutes to perform a set of index lookups
>> >> while java takes six seconds. The old version of bioseg took an hour. I
>> >> can't see anything in the GiST support code that could account for this.
>> >
>> > is the number of index lookups different, or just index lookup time is very
>> > big ?
>>
>> Same number of index lookups. Same algorithms. I have a set of 681879
>> segments, and I load them all into the index. I then query the index for
>> overlaps for each one in turn. For some reason, GiST lookups seem to be
>> slow, even if they are using a good algorithm. I have seen that problem
>> with btree_gist on integers too. I can't see any reason for this is the
>> GiST code - it all seems pretty tight to me. We probably need to do some
>> profiling.
>>
>> Matthew
>>
>> --
>> I suppose some of you have done a Continuous Maths course. Yes? Continuous
>> Maths? <menacing stares from audience> Whoah, it was like that, was it!
>> -- Computer Science Lecturer
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
> PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: GiST index performance
2010-02-25 23:44 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-02 16:23 ` Re: GiST index performance Robert Haas <robertmhaas@gmail.com>
@ 2010-03-03 01:13 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 17+ messages in thread
From: Bruce Momjian @ 2010-03-03 01:13 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; Oleg Bartunov <oleg@sai.msu.su>; Tom Lane <tgl@sss.pgh.pa.us>; Teodor Sigaev <teodor@sigaev.ru>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
Robert Haas wrote:
> On Thu, Feb 25, 2010 at 6:44 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Was this corrected? ?I don't see any commits to seg.c.
>
> I don't think this was ever reviewed.
>
> It seems like a good patch but I'd be skeptical of committing it now
> unless someone has the time to review it carefully. If not, let's add
> it to the next CF so we don't lose it again.
I have asked Oleg and Teodor to work on it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2010-03-22 14:23 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-25 23:42 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-02 16:21 ` Robert Haas <robertmhaas@gmail.com>
2010-03-15 15:58 ` Matthew Wakeling <matthew@flymine.org>
2010-03-16 01:58 ` Robert Haas <robertmhaas@gmail.com>
2010-03-16 16:18 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-17 09:26 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 16:31 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:20 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 20:49 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-19 21:16 ` Kenneth Marshall <ktm@rice.edu>
2010-03-20 16:16 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 13:29 ` Matthew Wakeling <matthew@flymine.org>
2010-03-22 14:02 ` Yeb Havinga <yebhavinga@gmail.com>
2010-03-22 14:23 ` Matthew Wakeling <matthew@flymine.org>
2010-02-25 23:44 Re: GiST index performance Bruce Momjian <bruce@momjian.us>
2010-03-02 16:23 ` Robert Haas <robertmhaas@gmail.com>
2010-03-03 01:13 ` Bruce Momjian <bruce@momjian.us>
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