Hi,
> henk de wit writes:
> > For some reason the mailinglist software seems to block the email as soon
> > as the planner details are in it, so I pasted those on pastebin.com:
> > http://pastebin.com/T5JTwh5T
>
> You need a less horrid estimate for the join size
Hi,
For some reason the mailinglist software seems to block the email as soon as
the planner details are in it, so I pasted those on pastebin.com:
http://pastebin.com/T5JTwh5T
Kind regards
On PG 9.1 and 9.2 I'm running the following query:
SELECT *FROM stream_store JOIN(SELECT
UNNEST(stream_store_ids) AS idFROM
stream_store_version_index WHERE stream_id = 607106 AND
version = 11) AS records USI
Hi there,
> henk de wit wrote:
> > I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS
> > I'm "pretty" sure there's really no other process that has the lock,
> as I'm the only one on a test DB.
> > If I execute the query
I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS 6.1) I'm
executing a simple "select ... for update" query:
SELECT
importing
FROM
customer
WHERE
id = :customer_id
FOR UPDATE NOWAIT
Once every 10 to 20 times Postgres fails to obtain the lock for no app
Hi,
I was looking at the support that PostgreSQL offers for table partitioning at
http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html. The concept
looks promising, but its maybe fair to say that PG itself doesn't really
supports partitioning natively, but one can simulate it using s
> $ apt-cache search iozone
> iozone3 - Filesystem and Disk Benchmarking Tool
You are right. I was confused with IOMeter, which can't be run on Linux (the
Dynamo part can, but that's not really useful without the 'command & control'
part).
__
> I've been using Bonnie++ for ages to do filesystem testing of new DB servers.
> But Josh Drake recently turned me on to IOZone.
Perhaps a little off-topic here, but I'm assuming you are using Linux to test
your DB server (since you mention Bonnie++). But it seems to me that IOZone
only has a
>> I still have some work to do to find out why dumping in the custom
>> format is so much slower.
>
> Offhand the only reason I can see for it to be much different from
> plain-text output is that -Fc compresses by default. If you don't
> care about that, try -Fc -Z0.
Ok, I did some performanc
Hi,
> henk de wit writes:
>> For performance reasons (obviously ;)) I'm experimenting with parallel
>> restore in PG 8.4. [...] I got this message however:
>> [...]
>> pg_restore: [archiver] WARNING: archive is compressed, but this
>> installation does not
Hi,
For performance reasons (obviously ;)) I'm experimenting with parallel restore
in PG 8.4. I grabbed the latest source snapshot (of today, March 30) and
compiled this with zlib support. I dumped a DB from PG 8.3.5 (using maximum
compression). I got this message however:
postg...@mymachine:/h
Hi,
>Has anyone done similar work in the light of upcoming many-core CPUs/systems?
>Any better results than 2x improvement?
Yes, in fact I've done a very similar thing on a quad CPU box a while back. In
my case the table in question had about 26 million rows. I did nothing special
to the table
Hi,
> On Tue, 10 Mar 2009, henk de wit wrote:
> > Now I wonder if there is any situation in which
> > sequential IO performance comes into play. E.g. perhaps during a
> > tablescan on a non-fragmented table, or during a backup or restore?
>
> Yes, up to a point.
Hi,
It is frequently said that for PostgreSQL the number 1 thing to pay attention
to when increasing performance is the amount of IOPS a storage system is
capable of. Now I wonder if there is any situation in which sequential IO
performance comes into play. E.g. perhaps during a tablescan on a
>You might also be interested in:
>
> http://thunk.org/tytso/blog/2009/02/20/aligning-filesystems-to-an-ssds-erase-block-size/
>
> http://thunk.org/tytso/blog/2009/02/22/should-filesystems-be-optimized-for-ssds/
Thanks a lot for the pointers. I'll definitely check these out.
> It seems you have
Hi,
I was reading a benchmark that sets out block sizes against raw IO performance
for a number of different RAID configurations involving high end SSDs (the
Mtron 7535) on a powerful RAID controller (the Areca 1680IX with 4GB RAM). See
http://jdevelopment.nl/hardware/one-dvd-per-second/
>From
Hi,> You can then> pull a TOC out with pg_restore and break that appart.
Reading the TOC is> pretty self evident. Once you get down to index creation
you can create> multiple files each with a group of indexes to create. Then
call> pg_restore multiple times in a script against the individual TO
Hi,When I try to restore a database dump on PostgreSQL 8.3 that's approximately
130GB in size and takes about 1 hour, I noticed index creation makes up the
bulk of that time. I'm using a very fast I/O subsystem (16 Mtron Pro 7535 SSDs
using a dual 1.2Ghz IOP/4GB cache RAID controller), fast CPU
> What do your various logs (pgsql, application, etc...) have to say?
There
is hardly anything helpful in the pgsql log. The application log
doesn't mention anything either. We log a great deal of information in
our application, but there's nothing out of the ordinary there,
although there's of co
> If the select returns a lot of data and you haven't enabled cursors (by
> calling setFetchSize), then the entire SQL response will be loaded in
> memory at once, so there could be an out-of-memory condition on the
> client.
I hear you. This is absolutely not the case though. There is no other ex
Hi,
We're currently having a problem with queries on a medium sized table. This
table is 22GB in size (via select pg_size_pretty(pg_relation_size('table'));).
It has 7 indexes, which bring the total size of the table to 35 GB (measured
with pg_total_relation_size).
On this table we're inserti
> > I wonder whether the current versions of postgres (i.e. either 8.2 or 8.3)
> > are able to utilize multiple cores for the execution of a single query?
> Nope.
I see, thanks for the clarification.
Btw, in this thread:
http://archives.postgresql.org/pgsql-performance/2007-10/msg00159.php
the
I wonder whether the current versions of postgres (i.e. either 8.2 or 8.3) are
able to utilize multiple cores for the execution of a single query?
This is one thing that systems like SQL Server and Oracle have been able to do
for quite some time. I haven't seen much in the documentation that hin
>select min(time) from transactions where payment_id is null
>So for that situation I tried whether a specific index helped, i.e. :
>create index transactions__time_payment_id__null__idx on transactions(time)
>where payment_id is null;
>But this does not really seem to help. It might be better to
> It's possible to combine independent indexes for resolving AND-type
> queries, but the combination process does not preserve ordering, so
> it's useless for this type of situation.
Ok, I'm going to try the double column index. Your suggestion about the index
with nulls left out worked great btw
> The only way I can see for that to be so slow is if you have a very
> large number of rows where payment_id is null --- is that the case?
The number of rows where payment_id is null is indeed large. They increase
every day to about 1 million at the end of the so-called "payment period" (so
cur
> > I have work_mem set to 256MB.
> Wow. That's inordinately high. I'd recommend dropping that to 32-43MB.
Ok, it seems I was totally wrong with the work_mem setting. I'll adjust it to a
more saner level. Thanks a lot for the advice everyone!
> Explain is your friend in that respect.
It sh
> select payment_id from transactions order by payment_id desc limit 1;
This one is indeed instant! Less than 50ms. In my case I can't use it for max
though because of the fact that payment_id can be null (which is an unfortunate
design choice). The other variant however didn't become instant.
> Do you have autovacuum turned on? With what settings?
Yes, I have it turned on. The settings are:
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 250
autovacuum_freeze_max_age 2
autovacuum_naptime 1min
autovacuum_vacuum_cost_delay
> It looks to me like you have work_mem set optimistically large. This
> query seems to be doing *many* large sorts and hashes:
I
have work_mem set to 256MB. Reading in PG documentation I now realize
that "several sort or hash operations might be running in parallel". So
this is most likely the
> This query takes a totally unrealistic amount of time for execution (I have
> it running for >30 minutes now on a machine with 8GB and 4 [EMAIL PROTECTED],
> and it still isn't finished).
To correct myself, I looked at the wrong window earlier, when I typed the email
the query had in fact fin
Hi,
I have a table with some 50 millions rows in PG 8.2. The table has indexes on
relevant columns. My problem is that most everything I do with this table
(which are actually very basic selects) is unbearable slow. For example:
select max(payment_id) from transactions
This takes 161 seconds.
> This error should have produced a map of per-context memory use in the>
> postmaster log.
> Please show us that.
I'm not exactly sure what to look for in the log. I'll do my best though and
see what I can come up with.
_
Expre
> How is the memory consumed? How are you measuring it? I assume you
> mean the postgres process that is running the query uses the memory.
> If so, which tool(s) are you using and what's the output that shows it
> being used?
It's periodically measured and recorded by a script from which the rel
Hi,
I'm running into a problem with PostgreSQL 8.2.4 (running on 32 bit Debian
Etch/2x dual core C2D/8GB mem). The thing is that I have a huge transaction
that does 2 things: 1) delete about 300.000 rows from a table with about 15
million rows and 2) do some (heavy) calculations and re-insert a
Perhaps one other interesting observation; when I earlier removed the status
check for which the rows got so wrongly estimated, the query got
dramatically faster. However, once I also remove all redundant checks the
query gets slower again.
This is the query with both status and redundant chec
Well, that's darn odd. It should not be getting that so far wrong.
I've been puzzling on this for over a week now, but can't seem to find a
solution. Would you have some more hints of what I could possibly try next?
As far as I can see, the mentioned status column is just a simple column. Or
Well, that's darn odd. It should not be getting that so far wrong.
What's the datatype of the status column exactly (I'm guessing varchar
but maybe not)? Would you show us the pg_stats row for the status column?
It has been created as a char(1) in fact. The pg_stats row for the status
column
One interesting other thing to note; if I remove the banners_links.status =
0 condition from the query altogether the execution times improve
dramatically again. The results are not correct right now, but if worse
comes to worst I can always remove the unwanted rows in a procedural
language (it
You might be able to improve matters by increasing the statistics target
for this table.
I have tried to increase the statistics for the status column to the maximum
of 1000. After that I performed an analyze, vacuum analyze and vacuum full
analyze on the table. Unfortunately this didn't seem
In the actual event, with
359 rows out of the scan, the nestloop way is just horrid because it
repeats the other side 359 times :-(
Indeed. :(
Btw, I tried to apply the removal of the redundant check in the larger query
(the one from which I extracted the part shown earlier) but it only perf
Since you have two redundant tests, the selectivity is being
double-counted, leading to a too-small rows estimate and a not very
appropriate choice of join plan.
I see, thanks for the explanation. I did notice though that in the second
case, with 1 redundant test removed, the estimate is still
In investigating a slow query, I distiled the code below from a larger
query:
SELECT
*
FROM
/* SUBQUERY banners */ (
SELECT
*
FROM
/* SUBQUERY banners_links */ (
SELECT
43 matches
Mail list logo
