On Thu, 2007-10-11 at 16:04 -0400, Merlin Moncure wrote:
On 10/11/07, Andrew - Supernews [EMAIL PROTECTED] wrote:
On 2007-10-10, Theo Kramer [EMAIL PROTECTED] wrote:
When doing a 'manual' prepare and explain analyze I get the following
rascal=# prepare cq (char(12), smallint, integer)
On Thu, 2007-10-11 at 13:28 -0400, Merlin Moncure wrote:
On 10/11/07, Theo Kramer [EMAIL PROTECTED] wrote:
On Thu, 2007-10-11 at 10:12 +0100, Richard Huxton wrote:
Theo Kramer wrote:
So I suspect that there is something more fundamental here...
OK, so there must be something
On Thu, 2007-10-11 at 18:28 +, Andrew - Supernews wrote:
On 2007-10-10, Theo Kramer [EMAIL PROTECTED] wrote:
When doing a 'manual' prepare and explain analyze I get the following
rascal=# prepare cq (char(12), smallint, integer) as SELECT oid,
calllog_mainteng, calllog_phase,
Theo Kramer wrote:
On Thu, 2007-10-11 at 18:28 +, Andrew - Supernews wrote:
When you do this from the application, are you passing it 3 parameters,
or 5? The plan is clearly taking advantage of the fact that the two
occurrences of $1 and $2 are known to be the same value; if your app is
On 10/12/07, Theo Kramer [EMAIL PROTECTED] wrote:
On Thu, 2007-10-11 at 16:04 -0400, Merlin Moncure wrote:
is this a converted cobol app?
:) - on the right track - it is a conversion from an isam based package
where I have changed the backed to PostgreSQL. Unfortunately there is
way too
On Fri, Oct 12, 2007 at 9:57 AM, in message
[EMAIL PROTECTED], Theo Kramer
[EMAIL PROTECTED] wrote:
select * from foo where
(a = a1 and b = b1 and c = c1) or
(a = a1 and b b1) or
(a a1)
order by a, b desc, c;
I have, however, found that transforming the above into a union
On Fri, 2007-10-12 at 09:02 -0400, Merlin Moncure wrote:
fwiw, I converted a pretty large cobol app (acucobol) to postgresql
backend translating queries on the fly. if this is a fresh effort,
you definately want to use the row-wise comparison feature of 8.2.
not only is it much simpler, it's
henk de wit [EMAIL PROTECTED] writes:
I indeed found them in the logs. Here they are:
It looks to me like you have work_mem set optimistically large. This
query seems to be doing *many* large sorts and hashes:
HashBatchContext: 262144236 total in 42 blocks; 3977832 free (40 chunks);
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.
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
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
On Fri, Oct 12, 2007 at 3:41 PM, in message
[EMAIL PROTECTED], henk de wit
[EMAIL PROTECTED] wrote:
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
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
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.
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 shows
On Oct 12, 2007, at 4:09 PM, henk de wit wrote:
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
On Oct 12, 2007, at 4:48 PM, henk de wit wrote:
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!
I wrote:
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?
There's not a lot you could do about that in existing releases :-(.
Actually, there is a possibility if you are willing to change the query:
make a
henk de wit [EMAIL PROTECTED] writes:
The plan looks like this:
Result (cost=3D0.37..0.38 rows=3D1 width=3D0) (actual time=3D184231.636..=
184231.638 rows=3D1 loops=3D1)
InitPlan
- Limit (cost=3D0.00..0.37 rows=3D1 width=3D8) (actual time=3D18423=
1.620..184231.622 rows=3D1
On Friday 12 October 2007, henk de wit [EMAIL PROTECTED] wrote:
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
henk de wit [EMAIL PROTECTED] writes:
I see, that sounds very interesting too. As you might have noticed, I'm not=
an expert on this field but I'm trying to learn. I was under the impressio=
n that the last few incarnations of postgresql automatically combined singl=
e column indexes for
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
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.
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 see
24 matches
Mail list logo
