- Original Message -
From: "Tomeh, Husam" <[EMAIL PROTECTED]>
To: "Adnan DURSUN" <[EMAIL PROTECTED]>;
Sent: Wednesday, October 04, 2006 4:29 AM
Subject: RE: [PERFORM] PostgreSQL Caching
Query plans are not stored in the shared buffers and therefore can not
be re-used by other session
>> * When any session updates the data that already in shared
buffer,
>>does Postgres synchronize the data both disk and shared buffers area
>> immediately ?
Not necessarily true. When a block is modified in the shared buffers,
the modified block is written to the Postgres WAL log. A peri
Adding -performance back in.
On Tue, Oct 03, 2006 at 05:10:04PM -0700, Ron Mayer wrote:
> Jim C. Nasby wrote:
> >
> > Index scans are also pretty picky about correlation. If you have really
> > low correlation you don't want to index scan,
>
> I'm still don't think "correlation" is the right met
Jim C. Nasby wrote:
>
> Index scans are also pretty picky about correlation. If you have really
> low correlation you don't want to index scan,
I'm still don't think "correlation" is the right metric
at all for making this decision.
If you have a list of addresses clustered by "zip"
the "correla
Thanks,
I wonder these ;
* When any session updates the data that allready in shared buffer,
does Postgres sychronize the data both disk and shared buffers area
immediately ?
* Does postgres cache SQL execution plan analyze results in memory
to use for other sessi
Mark Lewis <[EMAIL PROTECTED]> writes:
> Have you considered creating one partial index per assetid? Something
> along the lines of "CREATE INDEX asset_index_N ON asset_positions(ts)
> WHERE assetid=N"? I'd guess that the planner probably wouldn't be smart
> enough to use the partial indexes unle
Like many descent RDBMS, Postgresql server allocates its own shared
memory area where data is cached in. When receiving a query request,
Postgres engine checks first its shared memory buffers, if not found,
the engine performs disk I/Os to retrieve data from PostgreSQL data
files and place it in t
A few hundred is quite a lot for the next proposal and it's kind of an
ugly one, but might as well throw the idea out since you never know.
Have you considered creating one partial index per assetid? Something
along the lines of "CREATE INDEX asset_index_N ON asset_positions(ts)
WHERE assetid=N"?
Hi,
I wonder how PostgreSQL caches the SQL query results. For example ;
* does postgres cache query result in memory that done by session A
?
* does session B use these results ?
Best Regards
Adnan DURSUN
---(end of broadcast)
Not many. It fluctuates, but there are usually only ever a few hundred
at most. Each assetid has multi-millions of positions though.
Mark Lewis wrote:
Hmmm. How many distinct assetids are there?
-- Mark Lewis
On Tue, 2006-10-03 at 14:23 -0700, Graham Davis wrote:
The "summary table" app
Hmmm. How many distinct assetids are there?
-- Mark Lewis
On Tue, 2006-10-03 at 14:23 -0700, Graham Davis wrote:
> The "summary table" approach maintained by triggers is something we are
> considering, but it becomes a bit more complicated to implement.
> Currently we have groups of new positi
The "summary table" approach maintained by triggers is something we are
considering, but it becomes a bit more complicated to implement.
Currently we have groups of new positions coming in every few seconds or
less. They are not guaranteed to be in order. So for instance, a group
of position
Have you looked into a materialized view sort of approach? You could
create a table which had assetid as a primary key, and max_ts as a
column. Then use triggers to keep that table up to date as rows are
added/updated/removed from the main table.
This approach would only make sense if there were
Thanks Tom, that explains it and makes sense. I guess I will have to
accept this query taking 40 seconds, unless I can figure out another way
to write it so it can use indexes. If there are any more syntax
suggestions, please pass them on. Thanks for the help everyone.
Graham.
Tom Lane wr
Graham Davis <[EMAIL PROTECTED]> writes:
> How come an aggreate like that has to use a sequential scan? I know
> that PostgreSQL use to have to do a sequential scan for all aggregates,
> but there was support added to version 8 so that aggregates would take
> advantage of indexes.
Not in a GRO
On Tue, Oct 03, 2006 at 12:13:43 -0700,
Graham Davis <[EMAIL PROTECTED]> wrote:
> Also, the multikey index of (assetid, ts) would already be sorted and
> that is why using such an index in this case is
> faster than doing a sequential scan that does the sorting afterwards.
That isn't necessaril
How come an aggreate like that has to use a sequential scan? I know
that PostgreSQL use to have to do a sequential scan for all aggregates,
but there was support added to version 8 so that aggregates would take
advantage of indexes. This is why
SELECT max(ts) AS ts
FROM asset_positions;
Us
[EMAIL PROTECTED] (Graham Davis) writes:
> 40 seconds is much too slow for this query to run and I'm assuming
> that the use of an index will make it much faster (as seen when I
> removed the GROUP BY clause). Any tips?
Assumptions are dangerous things.
An aggregate like this has *got to* scan t
On 10/3/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
Please ignore sample 1 - now that I have the logging feature, I can see that
my query generator algorithm made an error.
can you do explain analyze on the two select queries on either side of
the union separatly? the subquery is correctly
Also, the multikey index of (assetid, ts) would already be sorted and
that is why using such an index in this case is
faster than doing a sequential scan that does the sorting afterwards.
Graham.
Chris Browne wrote:
[EMAIL PROTECTED] (Graham Davis) writes:
Adding DESC to both columns in
The asset_positions table has about 1.7 million rows, and this query
takes over 40 seconds to do a sequential scan. Initially I was trying
to get the original query:
SELECT assetid, max(ts) AS ts
FROM asset_positions
GROUP BY assetid;
to use the multikey index since I read that PostgreSQL
[EMAIL PROTECTED] (Graham Davis) writes:
> Adding DESC to both columns in the SORT BY did not make the query use
> the multikey index. So both
>
> SELECT DISTINCT ON (assetid) assetid, ts
> FROM asset_positions ORDER BY assetid, ts DESC;
>
> and
>
> SELECT DISTINCT ON (assetid) assetid, ts
> FROM
Hi,
Adding DESC to both columns in the SORT BY did not make the query use
the multikey index. So both
SELECT DISTINCT ON (assetid) assetid, ts
FROM asset_positions
ORDER BY assetid, ts DESC;
and
SELECT DISTINCT ON (assetid) assetid, ts
FROM asset_positions
ORDER BY assetid DESC, ts DESC
Please ignore sample 1 - now that I have the logging feature, I can see that
my query generator algorithm made an error.
The SQL of concern is now script 2.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
htt
I got one of these last Christmas. It works great, but the device has no
obvious power source and now I can't find my cat.
God help me when I accidently try to unsubscribe like that ..
Carlo
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Mon, Oct 02, 2006 at 01:36:17PM -0400,
On Mon, Oct 02, 2006 at 01:36:17PM -0400, uwcssa wrote:
> Please unsubscribe me! Thank you!
>
>
> Also, it would be better to have a message foot saying how to unsubscribe.
Will this do? It's too big for a footer.
Here's how to unsubscribe:
First, ask your Internet Provider to mail you an U
> explain analyze is more helpful because it prints the times.
Sorry, this runs in-line in my code, and I didn't want to slow the
already-slow program with explain analyze. I have run it outside of the code
in its own query. The new results are below.
> sample 1, couple questions:
> what is the
On 3 Oct 2006, at 16:04, Merlin Moncure wrote:
On 10/3/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
Some very helpful people had asked that I post the troublesome
code that was
generated by my import program.
I installed a SQL log feature in my import program. I have
posted samples of the
On October 3, 2006 05:08 am, Alexander Staubo wrote:
> On Oct 3, 2006, at 13:25 , Arnaud Lesauvage wrote:
> > The problem is that simple select queries with the primary key in
> > the WHERE statement take very long to run.
> > For example, this query returns only 7 rows and takes about 1
> > second
On October 3, 2006 04:25 am, Arnaud Lesauvage wrote:
> Hi List !
>
> I have a performance problem, but I am not sure whether it really
> is a problem or not.
> I am running a fresh install of PostgreSQL 8.1.4 on Windows2000.
> The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data
> fold
On 10/3/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
Some very helpful people had asked that I post the troublesome code that was
generated by my import program.
I installed a SQL log feature in my import program. I have
posted samples of the SQL statements that cause the biggest delays.
ex
uwcssa wrote:
Please unsubscribe me! Thank you!
Also, it would be better to have a message foot saying how to unsubscribe.
It would be better if you would have paid attention when you subscribed
as to how to unsubscribe.
--
Until later, Geoffrey
Those who would give up essential Liberty
Arnaud Lesauvage <[EMAIL PROTECTED]> writes:
>Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual
> time=0.023..0.734 rows=7 loops=1)
> Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84)
> OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid =
> 68) OR (gi
Tobias Brox writes:
> Oh - it is. How can you have a default value on a primary key? Will it
you can but it is useless :)
foo=# create table bar (uid int primary key default 0, baz text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for
table "bar"
CREATE TABLE
f
Tobias Brox wrote:
[Arnaud Lesauvage - Tue at 02:13:59PM +0200]
Tobias Brox wrote:
>Oh, the gid is not primary key. I guess I should also apologize for
>adding noise here :-)
Yes, it is a primary key, but I am the noise maker here ! ;-)
Oh - it is. How can you have a default value on a prim
[Arnaud Lesauvage - Tue at 02:13:59PM +0200]
> Tobias Brox wrote:
> >Oh, the gid is not primary key. I guess I should also apologize for
> >adding noise here :-)
>
> Yes, it is a primary key, but I am the noise maker here ! ;-)
Oh - it is. How can you have a default value on a primary key? Wil
Tobias Brox wrote:
Oh, the gid is not primary key. I guess I should also apologize for
adding noise here :-)
Yes, it is a primary key, but I am the noise maker here ! ;-)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ig
[Tobias Brox - Tue at 02:10:04PM +0200]
> Did you try "analyze" as well? It's weird it's using seq scan, since
> you have a primary key it's supposed to have an index ... though 500
> rows is little.
>
> I just checked up our own production database, takes 0.08 ms to fetch a
> row by ID from one
[Arnaud Lesauvage - Tue at 01:25:10PM +0200]
> I have a performance problem, but I am not sure whether it really
> is a problem or not.
> QUERY PLAN
> --
On Oct 3, 2006, at 13:25 , Arnaud Lesauvage wrote:
The problem is that simple select queries with the primary key in
the WHERE statement take very long to run.
For example, this query returns only 7 rows and takes about 1
second to run !
SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13
Steinar H. Gunderson wrote:
Total runtime: 0.801 ms
0.801 ms is _far_ under a second... Where do you have the latter timing from?
I fell stupid...
Sorry for the useless message...
>[]
---(end of broadcast)---
TIP 2: Don't 'kill -9' th
On Tue, Oct 03, 2006 at 01:25:10PM +0200, Arnaud Lesauvage wrote:
> For example, this query returns only 7 rows and takes about 1
> second to run !
>
> [...]
>
> Total runtime: 0.801 ms
0.801 ms is _far_ under a second... Where do you have the latter timing from?
/* Steinar */
--
Homepage: htt
Hi List !
I have a performance problem, but I am not sure whether it really
is a problem or not.
I am running a fresh install of PostgreSQL 8.1.4 on Windows2000.
The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data
folder is on a RAID-0 array of 2 SATA WD Raptor drives (10.000
rpm, 8
> Maybe "group by", "order by", "distinct on" and hand-written functions
> and aggregates (like first() or best()) may help.
We use these - we have lexical analysis functions which assign a rating to
each row in a set, and the likelyhood that the data is a match, and then we
sort our results.
I
> I still think that using a PL in the backend might be more performant
> than having an external client, alone being the SPI interface more
> efficient compared to the network serialization for external applications.
I would actually love for this to work better, as this is technology that I
wou
Some very helpful people had asked that I post the troublesome code that was
generated by my import program.
I installed a SQL log feature in my import program. I have
posted samples of the SQL statements that cause the biggest delays.
Thanks for all of your help.
Carlo
--
Sample 1:
Thi
Hi, Carlo,
Carlo Stonebanks wrote:
>> Trying to achieve a high level of data quality in one large project is
>> not often possible. Focus on the most critical areas of checking and get
>> that working first with acceptable performance, then layer on additional
>> checks while tuning. The complexi
Hi, Carlo,
Carlo Stonebanks wrote:
>> Did you think about putting the whole data into PostgreSQL using COPY in
>> a nearly unprocessed manner, index it properly, and then use SQL and
>> stored functions to transform the data inside the database to the
>> desired result?
>
> This is actually what
Hi All,
I reply to me, we solved a CPU Load problem. We had an external batch
who used an expensive SQL view and took 99% of the CPU.
Thanks all for you help !
---
I started the HAPlatform open-source project is a part of Share'nGo
Pro
Thanks Tom
The time difference did distract me from the issue. Switching Seq Scan to
off reduced the runtime greatly, so I am now adjusting the
effective_cache_size, random_page_cost settings to favor indexes over Seq
Scans.
Regards,
Tim
-Original Message-
From: Tom Lane [mailto:[EMAIL
50 matches
Mail list logo
