Hi there,
We've recently started seeing some context switch storm issues on our
primary Postgres database, and I was wondering if anyone had
encountered similar issues or had any ideas as to what could be
causing these issues.
The machine configuration is:
8xIntel Xeon Harpertown 5430 (2.66GHz)
Craig James <[EMAIL PROTECTED]> writes:
> Maybe this is an obviously dumb thing to do, but it looked reasonable
> to me. The problem is, the seemingly simple sort below causes a
> fairly powerful computer to completely freeze for 5-10 minutes.
trace_sort output might be informative.
I noticed that query
SELECT dok.*
FROM dok
JOIN (SELECT DISTINCT dokumnr FROM temptbl ) x USING(dokumnr);
is slow in 8.1.4
I cannot use explain analyze since this query uses results from
temporary table temptbl which is not available.
Generally if you know your temptbl will always contai
Generally if you know your temptbl will always contains a few rows (say,
generally a few and never more than a few thousands) it is better to use
something like that :
- get list of items
- SELECT * FROM table WHERE id IN (...)
My list can contain 1 .. 10 records and table contains 3
I noticed that query
SELECT dok.*
FROM dok
JOIN (SELECT DISTINCT dokumnr FROM temptbl ) x USING(dokumnr);
is slow in 8.1.4
I cannot use explain analyze since this query uses results from temporary
table temptbl which is not available.
Sometimes innter table returns only 1 row so maybe seq s
My list can contain 1 .. 10 records and table contains 300
records and is growing.
Ah. No IN(), then ;)
Temp table + ANALYZE seems your only option...
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
Has anyone benchmarked this controller (PCIe/4x, 512 MB BBC)? We try to
use it with 8x SATA 1TB drives in RAID-5 mode under Linux, and measure
strange values. An individual drive is capable of delivering 91 MB/sec
sequential read performance, and we get values ~102MB/sec out of a
8-drive RAID5,
My list can contain 1 .. 10 records and table contains 300
records and is growing.
Ah. No IN(), then ;)
Temp table + ANALYZE seems your only option...
In 8.3 or 8.4 I think that IN() or temp table produce exactly the same
result.
Andrus.
--
Sent via pgsql-performance mailing l
Don't reply to another message when starting a new thread. People will
miss your message.
Craig James wrote:
> Maybe this is an obviously dumb thing to do, but it looked reasonable to
> me.
Looks reasonable here too - except I'm not sure what I'd do with 2
million rows of sorted table in my conso
My list can contain 1 .. 10 records and table contains 300
records and is growing.
Ah. No IN(), then ;)
Temp table + ANALYZE seems your only option...
In 8.3 or 8.4 I think that IN() or temp table produce exactly the same
result.
Andrus.
Oh, I just thought about something,
Maybe this is an obviously dumb thing to do, but it looked reasonable to
me.
Looks reasonable here too - except I'm not sure what I'd do with 2
million rows of sorted table in my console. I'm guessing you're piping
the output into something.
Probably it's psql that is choking from bu
Craig James wrote:
Maybe this is an obviously dumb thing to do,
... and it was. I answered my own question: The problem came from using
psql(1) to do something I should have done with pg_dump.
but it looked reasonable to
me. The problem is, the seemingly simple sort below causes a fairly
Oh, I just thought about something, I don't remember in which version it
was added, but :
EXPLAIN ANALYZE SELECT sum(column1) FROM (VALUES ...a million
ntegers... ) AS v
Postgres is perfectly happy with that ; it's either a bit slow (about 1
second) or very fast depending on how you view th
Hi, all!
No one knows why I got "actual time=0.014..0.570 rows=30 loops=511222"
and "actual time=0.005..0.006 rows=1 loops=15158976"?
With:
cu.cal_user_type = 'u' AND
cu.cal_recur_date = COALESCE((SELECT cd.cal_start FROM egw_cal_dates
icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start = cd.cal
Often times, switching an inner subselect that requires a distinct to a group
by on that column yields better results. In this case, the IN should be
equivalent, so it probably will not help. This would look like:
SELECT dok.*
FROM dok
JOIN (SELECT dokumnr FROM temptbl GROUP BY dokumnr ) x
Scott,
Often times, switching an inner subselect that requires a distinct to a
group by on that column yields better results. In this case, the IN should
be equivalent, so it probably will not help. This would look like:
SELECT dok.*
FROM dok
JOIN (SELECT dokumnr FROM temptbl GROUP BY dok
Have you tried running ANALYZE on the temp table before selecting out of it?
That should give it the statistics necessary to correctly guess the output of a
group by on a single column.
ANALYZE temptbl;
SELECT dok.*
FROM dok
JOIN (SELECT dokumnr FROM temptbl GROUP BY dokumnr ) x USING(dokumn
17 matches
Mail list logo
