Ahmad,
what's about the number of unique words ? I mean stat() function.
Sometimes, it helps to identify garbage words.
How big is your articles (average length) ?
please, cut'n paste queries and output from psql ! How fast are
next queries ?
Oleg
On Fri, 23 Sep 2005, Ahmad Fajar wrot
K C Lau wrote:
I'm wondering if this performance issue is common enough for other users
to merit a fix in pg, especially as it seems that with MVCC, each of the
data records need to be accessed in addition to scanning the index.
Yes - there are certainly cases where index only access (or so
Dear Mark,
Thank you. That seems like a more manageable alternative if nothing else
works out. It should cover many of the OLTP update transactions. But it
does mean quite a bit of programming changes and adding another index on
all such tables, and it would not cover those cases when we need
eVl <[EMAIL PROTECTED]> writes:
>> You tell us --- let's see EXPLAIN ANALYZE results for both cases.
> Here EXPLAIN ANALYZE results for both queries attached.
The problem seems to be that the is_uaix() function is really slow
(somewhere around 4 msec per call it looks like). Look at the
first sc
K C Lau wrote:
Thank you all for your suggestions. I' tried, with some variations too,
but still no success. The times given are the best of a few repeated
tries on an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
A small denormalization, where you mark the row with the latest atdate
Hello pals, I have the following table in Postgresql 8.0.1
Mydb# \d geoip_block
Table "public.geoip_block"
Column| Type | Modifiers
-++---
locid | bigint |
start_block | inet |
end_block | inet |
mydb# explain analyze select locid from geoip_blo
"Gurpreet Aulakh" <[EMAIL PROTECTED]> writes:
> After further investigation I have found that the reason why the query is
> slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3.
> So the question comes down to : Why are hash and hash joins slower?
I looked into this a bit and de
From: Tom Lane <[EMAIL PROTECTED]>
Sent: Sep 23, 2005 2:15 PM
Subject: Re: [PERFORM] Releasing memory during External sorting?
>Mark Lewis <[EMAIL PROTECTED]> writes:
>> operations != passes. If you were clever, you could probably write a
>> modified bubble-sort algorithm that only made 2 passes
Yep. Also, bear in mind that the lg(n!)= ~ nlgn - n lower bound on
the number of comparisions:
a= says nothing about the amount of data movement used.
b= only holds for generic comparison based sorting algorithms.
As Knuth says (vol 3, p180), Distribution Counting sorts without
ever comparing ele
Mark Lewis <[EMAIL PROTECTED]> writes:
> operations != passes. If you were clever, you could probably write a
> modified bubble-sort algorithm that only made 2 passes. A pass is a
> disk scan, operations are then performed (hopefully in memory) on what
> you read from the disk. So there's no the
[EMAIL PROTECTED] (Stef) writes:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of postgres was the last version that had
> the i
operations != passes. If you were clever, you could probably write a
modified bubble-sort algorithm that only made 2 passes. A pass is a
disk scan, operations are then performed (hopefully in memory) on what
you read from the disk. So there's no theoretical log N lower-bound on
the number of dis
Ron Peacetree <[EMAIL PROTECTED]> writes:
> 2= No optimal external sorting algorithm should use more than 2 passes.
> 3= Optimal external sorting algorithms should use 1 pass if at all possible.
A comparison-based sort must use at least N log N operations, so it
would appear to me that if you have
Bruno Wolff III mentioned :
=> > => If you have a proper FSM setting you shouldn't need to do vacuum fulls
=> > => (unless you have an older version of postgres where index bloat might
=> > => be an issue).
Thanks Alvaro and Bruno
I just want to clarify something that I also couldn't
find a cle
After further investigation I have found that the reason why the query is
slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3.
So the question comes down to : Why are hash and hash joins slower? Is this
a postgres configuration setting that I am missing? Is the locale still
scr
On Fri, Sep 23, 2005 at 18:16:44 +0200,
Stef <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of post
On Fri, Sep 23, 2005 at 06:16:44PM +0200, Stef wrote:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of postgres was the last ve
From: Simon Riggs <[EMAIL PROTECTED]>
Sent: Sep 23, 2005 5:37 AM
Subject: [PERFORM] Releasing memory during External sorting?
>I have concerns about whether we are overallocating memory for use in
>external sorts. (All code relating to this is in tuplesort.c)
>
A decent external sorting algorithm,
Hi Oleg,
For single index I try this query:
explain analyze
select articleid, title, datee from articles
where fti @@ to_tsquery('bank&indonesia');
analyze result:
"Index Scan using fti_idx on articles (cost=0.00..862.97 rows=420 width=51)
(actual time=0.067..183761.324 rows=46
Bruno Wolff III mentioned :
=> If you have a proper FSM setting you shouldn't need to do vacuum fulls
=> (unless you have an older version of postgres where index bloat might
=> be an issue).
What version of postgres was the last version that had
the index bloat problem?
-
Hi
We are experiencing consistent slowness on the database for
one application. This is more a reporting type of application, heavy on the
bytea data type usage (gets rendered into PDFs in the app server). A lot of
queries, mostly selects and a few random updates, get accumulated on the
Simon Riggs <[EMAIL PROTECTED]> writes:
> Since we know the predicted size of the sort set prior to starting the
> sort node, could we not use that information to allocate memory
> appropriately? i.e. if sort size is predicted to be more than twice the
> size of work_mem, then just move straight to
On Fri, Sep 23, 2005 at 03:49:25PM +0200, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote:
Ok, that's great, but you didn't respond to the suggestion of using COPY
INTO instead of INSERT.
Part of the code I left out are some data conversions (e.g. from
path-to-fi
Dear Merlin,
At 20:34 05/09/23, Merlin Moncure wrote:
Can you time just the execution of this function and compare vs. pure
SQL version? If the times are different, can you do a exaplain analyze
of a prepared version of above?
esdt=> prepare test(character varying) as select atdate from playe
On Fri, 2005-09-23 at 10:09 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > If not, I would propose that when we move from qsort to tapesort mode we
> > free the larger work_mem setting (if one exists) and allocate only a
> > lower, though still optimal setting for the tapesort
Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> But do you maybe know a pointer to info, or tools that can measure, what
> my machine is doing during all the time it is doing nothing? Something
> like the performance monitor in Windows but than for Linux?
top, vmstat, iostat, sar, strace, oprofile
On Fri, 2005-09-23 at 10:33 -0400, Tom Lane wrote:
> Is the client code running on the same machine as the database server?
> If not, what's the network delay and latency between them?
Yes, it is running on the same machine.
> The major problem you're going to have here is at least one network
>
Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> I will test this a for perfomance improvement, but still, I wonder if ~
> 100 inserts/second is a reasonable performance for my software/hardware
> combination.
Is the client code running on the same machine as the database server?
If not, what's the
On Tue, Sep 20, 2005 at 14:53:19 -0400,
Markus Benne <[EMAIL PROTECTED]> wrote:
> I have a table that is purged by 25% each night. I'd like to do a
> vacuum nightly after the purge to reclaim the space, but I think I'll
> still need to do a vacuum full weekly.
>
> Would there be any benefit to
Simon Riggs <[EMAIL PROTECTED]> writes:
> If not, I would propose that when we move from qsort to tapesort mode we
> free the larger work_mem setting (if one exists) and allocate only a
> lower, though still optimal setting for the tapesort. That way the
> memory can be freed for use by other users
On 23-9-2005 15:35, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote:
Drop all of them and recreate them once the table is filled. Of course
that only works if you know your data will be ok (which is normal for
imports of already conforming data like datab
On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote:
> On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
> >On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
> I didn't say it was, did I?
No, you did not. But only last week someon'es head was (luckely for him
only virtua
eVl <[EMAIL PROTECTED]> writes:
> When executing this SELECT (see SELECT.A above) it executes in about
> 700 ms, but when I want wipe out all info about local traffic, with query
> like this:
> SELECT * FROM ( SELECT.A ) a WHERE type = 'global';
> It executes about 1 ms - more the
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote:
> Another suggestion:
> How many indexes and constraints are on the new table?
As mentioned in the first mail: in this tables only primary key
constraints, no other indexes or constraints.
> Drop all of them and recreate them once the
> At 02:07 05/09/23, Merlin Moncure wrote:
> > > >Here is a trick I use sometimes with views, etc. This may or may
not
> be
> > > >effective to solve your problem but it's worth a shot. Create
one
> small
> > > >SQL function taking date, etc. and returning the values and
define it
> > > >immutabl
At 19:15 05/09/23, Simon Riggs wrote:
select distinct on (PlayerID) PlayerID,AtDate from Player a
where PlayerID='0' order by PlayerId, AtDate Desc;
Does that work for you?
Best Regards, Simon Riggs
esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from
Player a where
Joost,
I presume you are using a relatively new jdbc driver. Make sure you
have added prepareThreshold=1 to the url to that it will use a named
server side prepared statement
You could also use your mod 100 code block to implement batch
processing of the inserts.
see addBatch, in jdbc
On Fri, 23 Sep 2005, Ahmad Fajar wrote:
Hi Oleg,
I didn't deny on the third repeat or more, it can reach < 600 msec. It is
only because the result still in postgres cache, but how about in the first
run? I didn't dare, the values is un-acceptable. Because my table will grows
rapidly, it's about
On 23-9-2005 13:05, Michael Stone wrote:
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
Ok, that's great, but you didn't respond to the suggestion of using COPY
INTO instead of INSERT.
But I have no clue where to begin with determining the bottleneck (it
even may be a norma
On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote:
> > > esdt=> explain analyze select PlayerID,AtDate from Player a
> > > where PlayerID='0' and AtDate = (select b.AtDate from Player b
> > > where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc
> > > LIMIT 1);
I think you
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
It's not clear what your object id generator does. If it's just a
sequence, it's not clear that you need this program at all--just use a
SELECT INTO and make the object id a
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
> It's not clear what your object id generator does. If it's just a
> sequence, it's not clear that you need this program at all--just use a
> SELECT INTO and make the object id a SERIAL.
It generates a GUID (and no, I do not want to turn this
On Fri, Sep 23, 2005 at 08:49:27AM +0200, Joost Kraaijeveld wrote:
3. Can I anyhow improve the performance without replacing my hardware,
e.g. by tweaking the software?
It's not clear what your object id generator does. If it's just a
sequence, it's not clear that you need this program at all--
I have concerns about whether we are overallocating memory for use in
external sorts. (All code relating to this is in tuplesort.c)
When we begin a sort we allocate (work_mem | maintenance_work_mem) and
attempt to do the sort in memory. If the sort set is too big to fit in
memory we then write to
Hi Joost,
why do you convert programmatically? I would do something like
create sequence s_objectid;
insert into
prototype.orders(objectid,ordernumber,orderdate,customernumber)
select next_val('s_objectid'),ordernummer, orderdatum, klantnummer from
odbc.orders
Sounds a lot faster to me.
/
Thank you all for your suggestions. I' tried, with some variations too, but
still no success. The times given are the best of a few repeated tries on
an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
For reference, only the following gets the record quickly:
esdt=> explain analyze selec
Ahmad,
how fast is repeated runs ? First time system could be very slow.
Also, have you checked my page
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
and some info about tsearch2 internals
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Oleg
On Thu
Hi,
I must convert an old table into a new table. The conversion goes at ~
100 records per second. Given the fact that I must convert 40 million
records, it takes too much time: more hours than the 48 hour weekend I
have for the conversion;-).
The tables are rather simple: both tables only have a
48 matches
Mail list logo