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 idx_trades_tid_part
"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
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 cluster.
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 working??
--- 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 d
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 not
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 s
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
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 PROTE
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 i
-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
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 shoul
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.
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
lik
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, da
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 ta
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
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
b
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
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 i
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 o
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...
Righ
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: http://www.acnc.com/0
-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 in
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
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-
> Fr
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 p
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
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
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.
Ind
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
"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 bra
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.
>
> >
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
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 have
-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 ar
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
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 wondering
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: 3Wa
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:
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 hi
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
