Bingo, the smaller the sort_mem, the faster that query is.
Thanks a lot to everybody that helped, i'll tweak with these values
more when I get a chance now that I have some guidelines that make
sense.
Rhett
On 8/9/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> Rhett Garber <[EMAIL PROTECTED]> writes:
Rhett Garber <[EMAIL PROTECTED]> writes:
> Well that could be an issue, is this abnormally large:
> #shared_buffers = 1536 # min 16, at least max_connections*2, 8KB each
> shared_buffers = 206440
> #sort_mem = 131072 # min 64, size in KB
> sort_mem = 524288 # min
Well that could be an issue, is this abnormally large:
#shared_buffers = 1536 # min 16, at least max_connections*2, 8KB each
shared_buffers = 206440
#sort_mem = 131072 # min 64, size in KB
sort_mem = 524288 # min 64, size in KB
vacuum_mem = 131072 #
Rhett Garber <[EMAIL PROTECTED]> writes:
> They are both running SuSE 8, 2.4.21-128-smp kernel
> Compile instructions (I didn't do it myself) indicate we built from
> source with nothing fancy:
You could double-check the configure options by running pg_config.
But probably the more interesting qu
> I'm now thinking you've got either a platform- or compiler-specific
> problem. Exactly what is the hardware (the CPU not the disks)? How did
> you build or come by the Postgres executables (compiler, configure
> options, etc)?
I've tried it on two of our machines, both HP Proliant DL580:
Produ
Rhett Garber <[EMAIL PROTECTED]> writes:
> Duplicated your setup in a separate DB.
> At least its reproducable for me.
Hmm. Well, we now have several data points but they seem to be on
wildly varying hardware. To try to normalize the results a little,
I computed the total actual time for the
Duplicated your setup in a separate DB.
At least its reproducable for me.
I tested this on a Xeon 2 Ghz, 1 Gig Ram. Its running on some shared
storage array that I'm not sure the details of.
My production example also shows up on our production machine that is
almost the same hardware but ha
Yes, sorry, two totally different machines. The 7.4.8
run was on a dual P4 3.2GHz, and the 7.4.2 run was on
a dual hyperthreaded Xeon 2.4GHz.
--Ian
On Tue, 2005-08-09 at 10:33, Tom Lane wrote:
> Ian Westmacott <[EMAIL PROTECTED]> writes:
> > On Mon, 2005-08-08 at 20:58, Tom Lane wrote:
Ian Westmacott <[EMAIL PROTECTED]> writes:
> On Mon, 2005-08-08 at 20:58, Tom Lane wrote:
>> I'd be interested to see results from other people using 7.4.* too.
> 7.4.8:
> Total runtime: 0.198 ms
> 7.4.2:
> Total runtime: 0.697 ms
Just to be clear: those are two different machines of different
On Mon, 2005-08-08 at 20:58, Tom Lane wrote:
> I'd be interested to see results from other people using 7.4.* too.
7.4.8:
QUERY
PLAN
On Mon, Aug 08, 2005 at 08:58:26PM -0400, Tom Lane wrote:
> I'd be interested to see results from other people using 7.4.* too.
I just built 7.4.1 on FreeBSD 4.11-STABLE and ran your test:
test=# explain analyze select rtmessagestate.* from rtmessagestate,connection
where (connection_registry_id
On Mon, Aug 08, 2005 at 08:58:26PM -0400, Tom Lane wrote:
> Hmph. There is something really strange going on here. I tried to
> duplicate your problem in 7.4.*, thus:
PostgreSQL 7.4.7 (Debian sarge):
regression=# explain analyze select rtmessagestate.* from
rtmessagestate,connection where (c
Rhett Garber <[EMAIL PROTECTED]> writes:
> This is postgres 7.4.1
> All the rows involved are integers.
Hmph. There is something really strange going on here. I tried to
duplicate your problem in 7.4.*, thus:
regression=# create table rtmessagestate(id int, f1 char(6));
CREATE TABLE
regression=
This is postgres 7.4.1
All the rows involved are integers.
Thanks,
Rhett
On 8/5/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> Rhett Garber <[EMAIL PROTECTED]> writes:
> > Hash Join (cost=5.96..7.04 rows=1 width=14) (actual
> > time=10.591..10.609 rows=1 loops=1)
> >Hash Cond: ("outer".id = "in
Rhett Garber <[EMAIL PROTECTED]> writes:
> Hash Join (cost=5.96..7.04 rows=1 width=14) (actual
> time=10.591..10.609 rows=1 loops=1)
>Hash Cond: ("outer".id = "inner".obj2)
>-> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14)
> (actual time=0.011..0.022 rows=5 loops=1)
>-
On 8/5/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote:
> Please post the explain analyze for both queries. From that we can see the
> predicted and the actual costs of them.
> select rtmessagestate.* from rtmessagestate, connection where
> connection_registry_id = 40105 and obj1 = 73582 and obj2
Rhett,
Please post the explain analyze for both queries. From that we can see the
predicted and the actual costs of them.
Regards,
Otto
- Original Message -
From: "Rhett Garber" <[EMAIL PROTECTED]>
To:
Sent: Friday, August 05, 2005 8:35 PM
Subject: [PERFORM] Why hash join instead
17 matches
Mail list logo
