On Mon, Sep 10, 2007 at 05:06:35PM -0700, Jason L. Buberel wrote:
I am considering moving to date-based partitioned tables (each table =
one month-year of data, for example). Before I go that far - is there
any other tricks I can or should be using to speed up my bulk data loading?
did you
I have a varchar ID field which captures a user account. If there is
no user id, then we just store the IP address of the user. Realizing
that we would like to index only those tuples where the user ID is not
an IP, I am trying to create a partial index as follows:
CREATE INDEX
Ron Johnson [EMAIL PROTECTED] writes:
On 09/10/07 19:50, Tom Lane wrote:
This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone information. I'm quite sure no such
thing is required
I was able get my database working again.
Never figured out why...
On Sep 11, 2007, at 12:52 AM, 李彦 Ian Li wrote:
Maybe some Debian specific commands will help:
pg_lsclusters: list clusters you have on the machine;
pg_dropcluster: drop an existing cluster;
pg_createcluster: create new
Phoenix Kiula wrote:
CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
WHERE trader_id ~ '[a-z]' ;
WHERE trader_id = 'johndoe'
It is not using this index at all! It is using no index in fact, it's
trying to do a sequential scan. Any ideas why this partial index is
not
--- Richard Huxton [EMAIL PROTECTED] wrote:
Phoenix Kiula wrote:
CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
WHERE trader_id ~ '[a-z]' ;
WHERE trader_id = 'johndoe'
It is not using this index at all! It is using no index in fact, it's
trying to do a sequential
Hello
We're trying to look for the most optimal config for a heavy duty
production server, and the following two are falling in the same price
range from our supplier:
Option 1:
2 x 300GB SCSI (10k rpm) with SAS and RAID 1
Option 2:
4 x 300GB SATA2 (7200 rpm, server grade) with RAID 10
I am
Richard Broersma Jr wrote:
--- Richard Huxton [EMAIL PROTECTED] wrote:
Phoenix Kiula wrote:
CREATE INDEX idx_trades_tid_partial ON trades (trader_id) WHERE
trader_id ~ '[a-z]' ; WHERE trader_id = 'johndoe'
It is not using this index at all! It is using no index in fact,
it's trying to do a
Tom Lane wrote:
Alban Hertroys [EMAIL PROTECTED] writes:
Richard Huxton wrote:
AFAIK there are two variants of ANY()
1. sets
2. arrays
So you should be able to do:
... WHERE x = ANY( ARRAY[a, b, c] )
But then the documentation isn't entirely correct. It suggests that it
works similar
It depends what you want to do with your database.
Do you have many reads (select) or a lot of writes (update,insert) ?
You should use a hardware raid controller with battery backup write cache
(write cache should be greater than 256 MB).
.. heavy duty production server ?
How much memory do you
On 11/09/2007, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
It depends what you want to do with your database.
Do you have many reads (select) or a lot of writes (update,insert) ?
This one will be a hugely INSERT thing, very low on UPDATEs. The
INSERTS will have many TEXT fields as they are
On 9/10/07, Ralph Smith [EMAIL PROTECTED] wrote:
I have a new install of 7.4 Ubuntu, and I'm looking for some advice on where
to get the libraries I need for the source configure and install to work.
Does anybody know off the top of their head where to look?
Thanks!
-
[EMAIL
On Sep 11, 2007, at 2:48 AM, Gregory Stark wrote:
Ron Johnson [EMAIL PROTECTED] writes:
On 09/10/07 19:50, Tom Lane wrote:
This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/11/07 07:55, Phoenix Kiula wrote:
On 11/09/2007, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
It depends what you want to do with your database.
Do you have many reads (select) or a lot of writes (update,insert) ?
This one will be a
Richard Huxton [EMAIL PROTECTED] writes:
The planner isn't smart enough to figure out which queries can use this
index by examining them, it just looks for (NOT paid) in the WHERE
clause and if it doesn't find it, ignores the index.
Well, it's a little bit brighter than that: it has some
This one will be a hugely INSERT thing, very low on UPDATEs. The
INSERTS will have many TEXT fields as they are free form data. So the
database will grow very fast. Size will grow pretty fast too.
You should use a hardware raid controller with battery backup write cache
(write cache should be
Hi
I've a problem with the GRANT on information_schema and view
key_column_usage.
Only a superuser is granted to see the record of this view (and other
views about index... see table_constraint)
What kind of permission is necessary for a simple user?
Thank you!
Marcello
On 11/09/2007, Tom Lane [EMAIL PROTECTED] wrote:
Richard Huxton [EMAIL PROTECTED] writes:
The planner isn't smart enough to figure out which queries can use this
index by examining them, it just looks for (NOT paid) in the WHERE
clause and if it doesn't find it, ignores the index.
Well,
On Tue, 11 Sep 2007, Phoenix Kiula wrote:
I'll have a raid controller in both scenarios, but which RAID should
be better: RAID1 or RAID10?
The point people are trying to make to you is that the differences between
RAID controllers can be as big as that between RAID architectures in cases
Hello,
I am in need of producing a query that has two count fields in it... Something
like:
select to_char(ts, 'MM/DD/') as day, str, proc,
(select count (*) as good from foobar where z != 0),
(select count (*) as bad from foobar where z = 0)
from foobar
where str != 9
group by str,
Hi,
I'm trying to get an avg value of 2 dates (to get to the month that most
part of an interval is in). I found SP's to generate rows from array
values, which I figured I could use with the avg aggregate, but to my
surprise:
* create or replace function explode_array(in_array anyarray) returns
i am trying to create a temp table inside a plpgsql function (i just
need a temporary place to hold data, but it is too complex for any other
data structure). unfortunately if i call the function again within the
same session the temp table still exists and the function fails. if i
drop the temp
select
to_char(ts, 'MM/DD/') as day,
str,
proc,
sum(case when z!=0 then 1 end) as good,
sum(case when z =0 then 1 end) as bad
from foobar
where str != 9
group by 1,2,3
order by 1
;
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
On 9/11/07, Jeff Lanzarotta [EMAIL PROTECTED] wrote:
I appreciate the help...
SELECT TO_CHAR(ts, 'MM/DD/') AS day, str, proc
, SUM(CASE
WHEN z 0
THEN 1
ELSE 0
END) AS good, 0 AS ajaa
, SUM(CASE
Hello.
We discovered some time ago that pgbouncer is NOT a balancer, because it
cannot spread connections/queries to the same database to multiple servers.
It's unbeliveable, but it's a fact! So, database name in the config MUST be
unique.
E.g. if we write
bardb = host=192.168.0.1 dbname=bardb
On Tue, Sep 11, 2007 at 05:50:38PM +0200, Alban Hertroys wrote:
* select avg(*) from explode_array(array[1, 3]);
avg
1.
(1 row)
avg(*) is not valid, same for sum(*) the reaosn you get the answer you
do it because postgres replaces the *
On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
select
to_char(ts, 'MM/DD/') as day,
str,
proc,
sum(case when z!=0 then 1 end) as good,
This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard or IS NOT DISTINCT FROM instead.
Remove the , 0 AS ajaa, that was some filler that got
thru by mistake.
---(end of broadcast)---
TIP 6: explain analyze is your friend
The point people are trying to make to you is that the differences between
RAID controllers can be as big as that between RAID architectures in cases
like yours. Which controller you're using and how the cache is setup can
have a larger impact on INSERT performance than how many/what type of
On 9/11/07, Alban Hertroys [EMAIL PROTECTED] wrote:
I would have expected an avg of 2.0 and a sum of 4, where am I going wrong?
This works for me:
select avg(a) from explode_array(array[1, 3]) a;
avg
2.
(1 row)
---(end of
George Pavlov [EMAIL PROTECTED] schrieb:
foo= select * from f();
ERROR: relation with OID 1469396 does not exist
CONTEXT: SQL statement SELECT a from t
PL/pgSQL function f line 4 at select into variables
the second invocation does not see the newly created temp table...
Right, normal
Thanks Greg.
You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a battery backup.
Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
-
Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/11/07 11:26, Phoenix Kiula wrote:
Thanks Greg.
You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a
On Tue, 2007-09-11 at 20:02 +0400, Dmitry Koterov wrote:
Hello.
We discovered some time ago that pgbouncer is NOT a balancer, because
it cannot spread connections/queries to the same database to multiple
servers. It's unbeliveable, but it's a fact! So, database name in the
config MUST be
Hi,
Is there a way to get the oracle's rank() over partition by queries in
postgresql? For example if I have a query like
Select Col1, Col2, RANK() OVER(PARTITION BY Col1 order by Col3 desc) as rank
from table1
Thanks in advance
Awesome, thanks...
George Pavlov [EMAIL PROTECTED] wrote: select
to_char(ts, 'MM/DD/') as day,
str,
proc,
sum(case when z!=0 then 1 end) as good,
sum(case when z =0 then 1 end) as bad
from foobar
where str != 9
group by 1,2,3
order by 1
;
-Original Message-
From:
On 12/09/2007, Ron Johnson [EMAIL PROTECTED] wrote:
How (on average) large are the records you need to insert, and how
evenly spread across the 24 hour day do the inserts occur?
There will be around 15,000 inserts in a day. Each insert will have
several TEXT columns, so it is difficult to
On 9/11/07, sharmi Joe [EMAIL PROTECTED] wrote:
Hi,
Is there a way to get the oracle's rank() over partition by queries in
postgresql? For example if I have a query like
Select Col1, Col2, RANK() OVER(PARTITION BY Col1 order by Col3 desc) as rank
from table1
Thanks in advance
See:
From: David Fetter [mailto:[EMAIL PROTECTED]
On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
sum(case when z!=0 then 1 end) as good,
This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard or IS NOT DISTINCT FROM instead.
and what
AFAIK PgBouncer is not a balancer but a connection pooler. Skype said
nothing about load balancing in its docs, so they are fair in this
sense. Why did you decide it should balance the load?
Regards,
Ivan
On 9/11/07, Dmitry Koterov [EMAIL PROTECTED] wrote:
Hello.
We discovered some time ago
On 9/11/07, Dmitry Koterov [EMAIL PROTECTED] wrote:
We discovered some time ago that pgbouncer is NOT a balancer, because it
cannot spread connections/queries to the same database to multiple servers.
It's unbeliveable, but it's a fact! So, database name in the config MUST be
unique.
Indeed,
On Tue, Sep 11, 2007 at 08:02:34PM +0400, Dmitry Koterov wrote:
So, it's completely magical for me why Session pooling, Transaction
pooling and Statement pooling options are exist (see
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer). If pgbouncer
is not a balancer, what purpose
George Pavlov [EMAIL PROTECTED] writes:
From: David Fetter [mailto:[EMAIL PROTECTED]
This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard or IS NOT DISTINCT FROM instead.
i do hate potential ambiguity... the != was something stuck in my brain
from old
On Tue, Sep 11, 2007 at 02:28:24PM -0400, Tom Lane wrote:
George Pavlov [EMAIL PROTECTED] writes:
From: David Fetter [mailto:[EMAIL PROTECTED]
This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard or IS NOT DISTINCT FROM instead.
i do hate
On Sep 11, 2007, at 13:42 , David Fetter wrote:
I believe that foo!=bar without white space should simply error out
because there is no reasonable, unambiguous way to parse it. Here's
what we get right now:
What's ambigious about it? An operator cannot include a space, so !=
(no space) is
Michael Glaesemann [EMAIL PROTECTED] writes:
What's ambigious about it? An operator cannot include a space, so !=
(no space) is *always* interpreted as one operator: not equals ().
Right. There are some corner cases though, for example
A*-5
which you'd probably rather weren't
Marcello Verona [EMAIL PROTECTED] writes:
I've a problem with the GRANT on information_schema and view
key_column_usage.
Only a superuser is granted to see the record of this view (and other
views about index... see table_constraint)
What kind of permission is necessary for a simple user?
I am making a first attempt at getting pam authentication working with a
postgres 8.2.4 installation on suse 10.2. I have created the file:
/etc/pam.d/postgresql:
authrequired/lib64/security/pam_ldap.so
account required/lib64/security/pam_ldap.so
and in my pg_hba.conf, I
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/11/07 12:02, Phoenix Kiula wrote:
On 12/09/2007, Ron Johnson [EMAIL PROTECTED] wrote:
How (on average) large are the records you need to insert, and how
evenly spread across the 24 hour day do the inserts occur?
There will be around
On 9/11/07, Phoenix Kiula [EMAIL PROTECTED] wrote:
Thanks Greg.
Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb
On Sep 11, 2007, at 5:49 AM, Tom Allison wrote:
I was able get my database working again.
Never figured out why...
My database data (sorry about the redundancy there) is sitting on a
RAID1 array with LVM and ReiserFS.
I've heard some dissention about the use of ReiserFS and was
On Wed, 12 Sep 2007, Phoenix Kiula wrote:
Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10:
On 12/09/2007, Greg Smith [EMAIL PROTECTED] wrote:
On Wed, 12 Sep 2007, Phoenix Kiula wrote:
Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate
On 9/12/07, Phoenix Kiula [EMAIL PROTECTED] wrote:
Just to confirm -- why do you say [Opteron] will have 2X as many
disks? In the dual-Opteron setup above I have 2 hard disks with
RAID1, whereas in the single-Xeon quad-core setup I have 4 disks with
RAID 10.
He didn't say that. Read his
On Wed, 12 Sep 2007, Phoenix Kiula wrote:
Just to confirm -- why do you say [Opteron] will have 2X as many
disks? In the dual-Opteron setup above I have 2 hard disks with
RAID1, whereas in the single-Xeon quad-core setup I have 4 disks with
RAID 10.
What I was trying to suggest was that the
56 matches
Mail list logo
