Re: [PERFORM] please comment on cpu 32 bit or 64 bit
[EMAIL PROTECTED] ("wisan watcharinporn") writes:
> please help me ,
> comment on postgresql (8.x.x) performance on cpu AMD, INTEL
> and why i should use 32 bit or 64 cpu ? (what the performance difference)
Generally speaking, the width of your I/O bus will be more important
to performance than the width of the processor bus.
That is, having more and better disk will have more impact on
performance than getting a better CPU.
That being said, if you plan to have a system with significantly more
than 2GB of memory, there seem to be pretty substantial benefits to
the speed of AMD memory bus access, and that can be quite significant,
given that if you have a lot of memory, and thus are often operating
out of cache, and are slinging around big queries, THAT implies a lot
of shoving data around in memory. AMD/Opteron has a way faster memory
bus than the Intel/Xeon systems.
But this is only likely to be significant if you're doing processing
intense enough that you commonly have >> 4GB of memory in use.
If not, then you'd better focus on I/O speed, which is typically
pretty independent of the CPU...
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/rdbms.html
"Anyway I know how to not be bothered by consing on the fly."
-- Dave Moon
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] CHECK vs REFERENCES
On Fri, 9 Sep 2005, Michael Fuhr wrote: On Sat, Sep 10, 2005 at 12:23:19AM -0300, Marc G. Fournier wrote: Which is faster, where the list involved is fixed? My thought is that since it doesn't have to check a seperate table, the CHECK itself should be the faster of the two, but I can't find anything that seems to validate that theory ... Why not just benchmark each method as you intend to use them? Here's a simplistic example: CREATE TABLE test_none ( val integer NOT NULL ); CREATE TABLE test_check ( val integer NOT NULL CHECK (val IN (1, 2, 3, 4, 5)) ); CREATE TABLE test_vals ( id integer PRIMARY KEY ); INSERT INTO test_vals SELECT * FROM generate_series(1, 5); CREATE TABLE test_fk ( val integer NOT NULL REFERENCES test_vals ); \timing INSERT INTO test_none SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 23578.853 ms Yowch, I expected CHECK to be better ... but not so significantly ... I figured I'd be saving milliseconds, which, on a busy server, would add up fast ... but not 10k' of milliseconds ... Thanks, that definitely shows a major benefit ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] CHECK vs REFERENCES
On Sat, Sep 10, 2005 at 12:23:19AM -0300, Marc G. Fournier wrote: > Which is faster, where the list involved is fixed? My thought is that > since it doesn't have to check a seperate table, the CHECK itself should > be the faster of the two, but I can't find anything that seems to validate > that theory ... Why not just benchmark each method as you intend to use them? Here's a simplistic example: CREATE TABLE test_none ( val integer NOT NULL ); CREATE TABLE test_check ( val integer NOT NULL CHECK (val IN (1, 2, 3, 4, 5)) ); CREATE TABLE test_vals ( id integer PRIMARY KEY ); INSERT INTO test_vals SELECT * FROM generate_series(1, 5); CREATE TABLE test_fk ( val integer NOT NULL REFERENCES test_vals ); \timing INSERT INTO test_none SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 23578.853 ms -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] CHECK vs REFERENCES
Which is faster, where the list involved is fixed? My thought is that since it doesn't have to check a seperate table, the CHECK itself should be the faster of the two, but I can't find anything that seems to validate that theory ... The case is where I just want to check that a value being inserted is one of a few possible values, with that list of values rarely (if ever) changing, so havng a 'flexible list' REFERENCED seems relatively overkill ... Thoughts, or pointers to a doc that disproves, or proves, what I believe? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] please comment on cpu 32 bit or 64 bit
"wisan watcharinporn" <[EMAIL PROTECTED]> writes: > comment on postgresql (8.x.x) performance on cpu AMD, INTEL > and why i should use 32 bit or 64 cpu ? (what the performance difference) For most database applications, you're better off spending your money on faster disk drives and/or more RAM than on a sexier CPU. Maybe your application doesn't follow that general rule --- but since you told us exactly zero about what your application is, this advice is worth what you paid for it ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Too slow query, do you have an idea to optimize?
Generate them all into a table and just delete them as you use them.
It's only 1 rows...
Chris
Choe, Cheng-Dae wrote:
> I need to generate unused random id with format is ID[0-9]{4}
> so i write below query but it seems to be too slow
>
> SELECT * FROM (
> SELECT user_id FROM (
> SELECT 'ID' || LPAD(r, 4, '0') AS user_id
> FROM generate_series(1, ) as r) AS s
> EXCEPT
> SELECT user_id FROM account ) AS t
> ORDER BY random()
> LIMIT 1
>
> and I execute explain analyze query.
> --
> Limit (cost=318.17..318.17 rows=1 width=32) (actual
> time=731.703..731.707 rows=1 loops=1)
>-> Sort (cost=318.17..318.95 rows=312 width=32) (actual
> time=731.693..731.693 rows=1 loops=1)
> Sort Key: random()
> -> Subquery Scan t (cost=285.79..305.24 rows=312 width=32)
> (actual time=424.299..659.193 rows= loops=1)
>-> SetOp Except (cost=285.79..301.35 rows=311
> width=16) (actual time=424.266..566.254 rows= loops=1)
> -> Sort (cost=285.79..293.57 rows=3112
> width=16) (actual time=424.139..470.529 rows=12111 loops=1)
>Sort Key: user_id
>-> Append (cost=0.00..105.24 rows=3112
> width=16) (actual time=5.572..276.485 rows=12111 loops=1)
> -> Subquery Scan "*SELECT* 1"
> (cost=0.00..30.00 rows=1000 width=4) (actual time=5.565..149.615
> rows= loops=1)
>-> Function Scan on
> generate_series r (cost=0.00..20.00 rows=1000 width=4) (actual
> time=5.553..63.224 rows= loops=1)
> -> Subquery Scan "*SELECT* 2"
> (cost=0.00..75.24 rows=2112 width=16) (actual time=0.030..28.473
> rows=2112 loops=1)
>-> Seq Scan on account
> (cost=0.00..54.12 rows=2112 width=16) (actual time=0.019..10.155
> rows=2112 loops=1)
> Total runtime: 738.809 ms
>
>
> do you have any idea for optimize?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
