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
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
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
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
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
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,
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
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:
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.
Generally if you know your temptbl will always contai
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
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 <[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.
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
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,
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 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
17 matches
Mail list logo
