On Fri, Jan 20, 2006 at 05:46:34PM -0500, Ron wrote:
> For an even more extreme speedup, don't most modern CPUs have an asm
> instruction that counts the bits (un)set (AKA "population counting")
> in various size entities (4b, 8b, 16b, 32b, 64b, and 128b for 64b
> CPUs with SWAR instructions)?
Tom Lane wrote:
Well, we're trying to split an index page that's gotten full into two
index pages, preferably with approximately equal numbers of items in
each new page (this isn't a hard requirement though). ... If that's
correct, what you really want is to divide the values so that the unions
On Fri, Jan 20, 2006 at 07:23:10PM -0500, Tom Lane wrote:
> I'm not very clear on what tsearch2 is doing with these bitmaps, but it
> looks like an upper page's downlink has the union (bitwise OR) of the
> one-bits in the values on the lower page, and you have to visit the lower
> page if this unio
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> On Fri, Jan 20, 2006 at 06:52:37PM -0500, Tom Lane wrote:
>> It's also worth considering that the entire approach is a heuristic,
>> really --- getting the furthest-apart pair of seeds doesn't guarantee
>> an optimal split as far as I can see. M
On Fri, Jan 20, 2006 at 06:52:37PM -0500, Tom Lane wrote:
> It's also worth considering that the entire approach is a heuristic,
> really --- getting the furthest-apart pair of seeds doesn't guarantee
> an optimal split as far as I can see. Maybe there's some totally
> different way to do it.
For
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> For the record: Could we do with a less-than-optimal split here?
Yeah, I was wondering the same. The code is basically choosing two
"seed" values to drive the index-page split. Intuitively it seems that
"pretty far apart" would be nearly as go
On Fri, Jan 20, 2006 at 04:50:17PM -0500, Tom Lane wrote:
> I wonder if there is a way to improve on that.
http://www.cs.uwaterloo.ca/~tmchan/slide_isaac.ps:
The diameter problem has been studied extensively in the traditional model.
Although O(n log n) algorithms have been given for d = 2 an
On Fri, Jan 20, 2006 at 05:50:36PM -0500, Tom Lane wrote:
> Yeah, but fetching from a small constant table is pretty quick too;
> I doubt it's worth getting involved in machine-specific assembly code
> for this. I'm much more interested in the idea of improving the
> furthest-distance algorithm in
On Fri, Jan 20, 2006 at 05:29:46PM -0500, Ron wrote:
> If the N-dimensional space is Euclidean (any is the same
> distance apart in dimension x), then finding the farthest pair can be
> done in at least O(n).
That sounds a bit optimistic.
http://portal.acm.org/ft_gateway.cfm?id=167217&type=
On Fri, Jan 20, 2006 at 05:46:34PM -0500, Ron wrote:
> At 04:37 PM 1/20/2006, Martijn van Oosterhout wrote:
> >Given that all it's doing is counting bits, a simple fix would be to
> >loop over bytes, use XOR and count ones. For extreme speedup create a
> >lookup table with 256 entries to give you t
Ron <[EMAIL PROTECTED]> writes:
> For an even more extreme speedup, don't most modern CPUs have an asm
> instruction that counts the bits (un)set (AKA "population counting")
> in various size entities (4b, 8b, 16b, 32b, 64b, and 128b for 64b
> CPUs with SWAR instructions)?
Yeah, but fetching fr
At 04:37 PM 1/20/2006, Martijn van Oosterhout wrote:
On Fri, Jan 20, 2006 at 04:19:15PM -0500, Tom Lane wrote:
> % cumulative self self total
> time seconds secondscalls Ks/call Ks/call name
> 98.96 1495.93 1495.93 33035195 0.00 0.00 hemdistsign
On Fri, Jan 20, 2006 at 04:50:17PM -0500, Tom Lane wrote:
> (hemdistcache calls hemdistsign --- I think gprof is doing something
> funny with tail-calls here, and showing hemdistsign as directly called
> from gtsvector_picksplit when control really arrives through hemdistcache.)
It may be the comp
At 05:16 PM 1/20/2006, Steinar H. Gunderson wrote:
On Fri, Jan 20, 2006 at 04:50:17PM -0500, Tom Lane wrote:
> I wonder if there is a way to improve on that.
Ooh, the farthest pair problem (in an N-dimensional vector space, though).
I'm pretty sure problems like this has been studied quite exten
On Fri, Jan 20, 2006 at 04:50:17PM -0500, Tom Lane wrote:
> I wonder if there is a way to improve on that.
Ooh, the farthest pair problem (in an N-dimensional vector space, though).
I'm pretty sure problems like this has been studied quite extensively in the
literature, although perhaps not with t
lockstat is available in Solaris 9. That can help you to determine if
there are any kernel level locks that are occuring during that time.
Solaris 10 also has plockstat which can be used to identify userland
locks happening in your process.
Since you have Solaris 9, try the following:
You ca
Hello;
I am going through a post mortem analysis of an infrequent but
recurring problem on a Pg 8.0.3 installation. Application code
connects to Pg using J2EE pooled connections.
PostgreSQL 8.0.3 on sparc-sun-solaris2.9, compiled by GCC
sparc-sun-solaris2.8-gcc (GCC) 3.3.2
Database is quite
Martijn van Oosterhout writes:
> Given that all it's doing is counting bits, a simple fix would be to
> loop over bytes, use XOR and count ones. For extreme speedup create a
> lookup table with 256 entries to give you the answer straight away...
Yeah, I just finished doing that and got about a 20
On Fri, Jan 20, 2006 at 10:37:54PM +0100, Martijn van Oosterhout wrote:
> Given that all it's doing is counting bits, a simple fix would be to
> loop over bytes, use XOR and count ones. For extreme speedup create a
> lookup table with 256 entries to give you the answer straight away...
For extra o
On Fri, Jan 20, 2006 at 04:19:15PM -0500, Tom Lane wrote:
> % cumulative self self total
> time seconds secondscalls Ks/call Ks/call name
> 98.96 1495.93 1495.93 33035195 0.00 0.00 hemdistsign
> So we gotta fix hemdistsign ...
lol!
On Fri, Jan 20, 2006 at 08:38:23PM +0100, Rikard Pavelic wrote:
> This would solve problems with prepare which is per session, so for
> prepared function to be
> optimal one must use same connection.
If you're dealing with something that's performance critical you're not
going to be constantly re
Well, I feel like a fool, because I failed to notice that the total
runtime shown in that profile wasn't anywhere close to the actual wall
clock time. gprof is indeed simply not counting the time spent in
dynamically-linked code. With tsearch2 statically linked into the
backend, a more believable
On Fri, Jan 20, 2006 at 03:21:45PM -0500, Tom Lane wrote:
> If the totals given by gprof are correct, then it's down in the noise.
> I don't think I trust that too much ... but I don't see anything in the
> gprof manual about how to include a dynamically loaded library in the
> profile. (I did com
Martijn van Oosterhout writes:
> Something I'm missing is the calls to tsearch functions. I'm not 100%
> familiar with gprof, but is it possible those costs have been added
> somewhere else because it's in a shared library? Perhaps the costs went
> into FunctionCall1/3?
I think that the tsearch f
On Fri, Jan 20, 2006 at 02:14:29PM -0500, Tom Lane wrote:
> [ thread moved to pgsql-performance ]
>
> I've obtained a gprof profile on Stephan's sample case (many thanks for
> providing the data, Stephan). The command is
Something I'm missing is the calls to tsearch functions. I'm not 100%
fam
Done, and paragraph added to 8.1.X. (7.3 mention retained for 8.1.X.)
---
Tom Lane wrote:
> Bruce Momjian writes:
> >
> > !Genetic query optimization (GEQO) randomly
> > !tests execution plans. Therefore, wh
Jim C. Nasby wrote:
My college professor said it, it must be true! ;P
The famous joke ;)
My understanding is that in plpgsql, 'bare' queries get prepared and act
like prepared statements. IE:
SELECT INTO variable
field
FROM table
WHERE condition = true
;
Unfortunately I don
[ thread moved to pgsql-performance ]
I've obtained a gprof profile on Stephan's sample case (many thanks for
providing the data, Stephan). The command is
CREATE INDEX foo ON publications_test USING gist (fti_title);
where fti_title is a tsvector column. There are 236984 rows in the
tabl
On Fri, Jan 20, 2006 at 07:50:23PM +0100, Rikard Pavelic wrote:
> >>Hi,
> >>
> >>Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work
> >>any
> >>faster by putting them into a stored procedure?
>
> >
> >IMHO no, why do you think so? You can use PREPARE instead, if you have m
Send query, output of EXPLAIN and table definitions.
On Fri, Jan 20, 2006 at 07:32:34PM +0100, Antoine wrote:
> Hi,
> I have a query that does a left outer join. The query gets some text
> from a reference table where one of the query's main tables may or may
> not have the text's tables id. It
On Fri, Jan 20, 2006 at 06:46:45PM +0100, [EMAIL PROTECTED] wrote:
> >Sure... I've never been asked to consult on our stuff, and in any case,
> >I don't do web front-ends (one of the nice things about working with a
> >team of other consultants). AFAIK IIS will happily talk to PostgreSQL
> >(though
Hi,
Will simple queries such as "SELECT * FROM blah_table WHERE tag='x';
work any
faster by putting them into a stored procedure?
IMHO no, why do you think so? You can use PREPARE instead, if you have
many
selects like this.
I tought that creating stored procedures in database means
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Fri, Jan 20, 2006 at 12:35:36PM +0800, K C Lau wrote:
> Here's the problem... the estimate for the backwards index scan is *way*
> off:
>> -> Limit (cost=0.00..1.26 rows=1 width=4) (actual
>> time=200032.928..200032.931 rows=1 loops=1)
>> -> Inde
Hi,
Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work any
faster by putting them into a stored procedure?
IMHO no, why do you think so? You can use PREPARE instead, if you have many
selects like this.
I tought that creating stored procedures in database means
stor
Hi,
I have a query that does a left outer join. The query gets some text
from a reference table where one of the query's main tables may or may
not have the text's tables id. It wasn't super fast, but now it simply
won't execute. It won't complete either through odbc or via pgadmin
(haven't ye
Sure... I've never been asked to consult on our stuff, and in any case,
I don't do web front-ends (one of the nice things about working with a
team of other consultants). AFAIK IIS will happily talk to PostgreSQL
(though maybe I'm wrong there...)
iis (yeah, asp in a successfull productive enviro
On Fri, Jan 20, 2006 at 09:37:50AM -0800, Joshua D. Drake wrote:
>
> >I lay no claim to our infrastructure. :)
> >
> Can I quote the: Pervasive Senior Engineering Consultant on that?
Sure... I've never been asked to consult on our stuff, and in any case,
I don't do web front-ends (one of the ni
I lay no claim to our infrastructure. :)
Can I quote the: Pervasive Senior Engineering Consultant on that?
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-A
On Fri, Jan 20, 2006 at 09:31:14AM -0800, Joshua D. Drake wrote:
> Jim C. Nasby wrote:
> >BTW, given all the recent discussion about vacuuming and our MVCC,
> >http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
> >should prove interesting. :)
> >
> Please explain...
Jim C. Nasby wrote:
BTW, given all the recent discussion about vacuuming and our MVCC,
http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
should prove interesting. :)
Please explain... what is the .asp extension. I have yet to see it
reliable in production ;)
On Fri, Jan 20, 2006 at 12:35:36PM +0800, K C Lau wrote:
Here's the problem... the estimate for the backwards index scan is *way*
off:
> -> Limit (cost=0.00..1.26 rows=1 width=4) (actual
> time=200032.928..200032.931 rows=1 loops=1)
>-> Index Scan Backward using pk_log on
>
BTW, given all the recent discussion about vacuuming and our MVCC,
http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
should prove interesting. :)
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork:
Bruce Momjian writes:
>
> !Genetic query optimization (GEQO) randomly
> !tests execution plans. Therefore, when the number of tables
> !exceeds geqo and genetic query optimization is in use,
> !the execution plan will change each time the statement is executed.
>
geqo
Simon Riggs wrote:
> On Wed, 2006-01-11 at 22:23 -0500, Tom Lane wrote:
> > =?iso-8859-1?Q?Jean-Philippe_C=F4t=E9?= <[EMAIL PROTECTED]> writes:
> > > Thanks a lot for this info, I was indeed exceeding the genetic
> > > optimizer's threshold. Now that it is turned off, I get
> > > a very stable res
On Fri, 2006-01-20 at 18:14 +0900, James Russell wrote:
> I am looking to speed up performance, and since each page executes a
> static set of queries where only the parameters change, I was hoping
> to take advantage of stored procedures since I read that PostgreSQL's
> caches the execution plans
you could use pgpool
http://pgpool.projects.postgresql.org/
On 1/20/06, James Russell <[EMAIL PROTECTED]> wrote:
> Hi there,
>
> I am running a website where each page connects to the DB to retrieve and
> write information. Each page load uses a separate connection (rather than
> just sharing o
Hi there,
I am running a website where each page connects to the DB to retrieve
and write information. Each page load uses a separate connection
(rather than just sharing one as is the common case) because I use a
lot of transactions.
I am looking to speed up performance, and since each page exec
47 matches
Mail list logo
