Matthew Wakeling wrote:
Thomas, the order of columns in the index matters. The index is
basically a tree structure, which resolves the left-most column before
resolving the column to the right of it. So to answer your query, it
will resolve the city_id, then it will have to scan almost all of t
Dont worry about it, this is just for performance testing.
thomas
> Thomas Finneid wrote:
>> Hi
>>
>> I am wondering what stripe size, on a raid 0, is the most suitable for
>> postgres 8.2?
>>
>
> Hello
>
> Raid 0 for a database? This is a disaster
data for a single street, when I want it for all streets.
Greg Smith wrote:
On Tue, 12 May 2009, Thomas Finneid wrote:
on a database with 260 GB of data and an index size of 109GB on
separate raid disks. there are
85 city_ids, 2000
street_ids per city,
20 house_ids per
Hi
I am wondering what stripe size, on a raid 0, is the most suitable for
postgres 8.2?
I read a performance tutorial by Bruce Momjian and it suggest setting
the stripe size to the same block size (as pg uses?)
( http://momjian.us/main/writings/pgsql/hw_performance/index.html )
But I want to
Hi
have the following table (theoretical)
table apartment_location (
city_idint,
street_id int,
house_id int,
floor_id int,
owner string
...
)
index .. ( city_id, street_id, house_id, floor_id ) tablespc indexspace;
on a database w
Joshua D. Drake wrote:
On Sat, 2009-02-07 at 01:52 +0100, Thomas Finneid wrote:
>>
effective_cache_size
>>
This is just a hint to tell the planner how much cache will generally be
available.
ok, but available for what?
The number should be reflective of your shared buffers
Hi
I am having some problem understanding the details of a couple of
configuration directives. Here is what I think I understand it so far:
effective_cache_size
- specifies in how many B/KB/MB will be used to cache data
between pg and the OS?
- what is actually cache
Kenneth Marshall wrote:
It may be that the smaller index has update contention for the same
blocks that the larger index does not.
Is that an assumption based on both indexes existing? if so I might
agree, but if you are talking about only one index existing at a time
then could you explain w
Scott Marlowe wrote:
I'm guessing that you just had more data in the table or something by
the time you tested that, or some cron job was running in the
background, or some other issue, not the index.
It starts from scratch and builds up. Every insert has constant time
from the first to the l
Scott Marlowe wrote:
On Sun, Jan 25, 2009 at 1:14 AM, Thomas Finneid wrote:
Scott Marlowe wrote:
So I don't think you've found the cause of your problem with the smaller
index.
Ok I understand, but why dont you think the index is the problem?
If so, I did the test with both
Scott Marlowe wrote:
Also, what other kind of usage patterns are going on.
For this test there was nothing else going on, it was just that one
writer. The complete usage pattern is that there is one writer that
writes this data, about 2 rows per second, and then a small number
of reader
Scott Marlowe wrote:
On Sat, Jan 24, 2009 at 3:57 AM, Thomas Finneid wrote:
>
Is this table constantly growing, or is it at a stable plateu? I'd
assume a constantly growing table, or one with bloat problems would
get slower over time. About how many rows does this table have?
The
Hi
I just experienced a performance loss on inserts when redefining my
index on a table.
I have a database with the following table
table:
id1 int
id2 int
id3 int
id4 int
val1 float
tablespace dbspace
the data id distribution is hierarchical and even, well fitti
Hi
I have noticed that my transaction log has quite large activity volume
(up to 15MB per transaction), so with the amount of data I am using I
have manually moved the pg_xlog directory to a different disk. This
allows me to have both the table space and transaction log on two
different high
Thomas Markus wrote:
try to reorganize your data with CLUSTER and create appropriate indixes
(dont forget to check statistics).
One question. Assume I have clustered and new data has been added after
that, according to the docs that data is added "outside" of the
clustered data. What happens
(Sorry, did not include the list in the reply)
Pavan Deolasee wrote:
Yes. That's how it works.
Is that how it works for an index as well? I just found out that I have
an index that is 35GB, and the table is 85GB. ( I will look into the
index, it works fine, but an index that is almost one
Pavan Deolasee wrote:
On Thu, Jan 22, 2009 at 4:42 PM, Thomas Finneid wrote:
As I understand it, data is stored in pages and those pages have to be
retrieved in order to write or read data from them. So my assumption is that
a page used to write data would not be replaced until memory is low
Hi
A quick question, when pg receives data to be written to a table, does
it cache that data in memory in case a subsequent request/query would
need it?
As I understand it, data is stored in pages and those pages have to be
retrieved in order to write or read data from them. So my assumption
Hi I am developing a database and have a couple of questions I havent
found an answer to yet.
1) how do I find the size of an index, i.e. the size on disk?
2) I have a query that is taking a long time to complete because the
table is about 120GB large. Its only returning 2000 rows, so in
pr
Greg Smith wrote:
In practice, the "ordered"
mode (the default for ext3) seems sufficient to prevent database
corruption. There is a substantial performance hit to running in full
journal mode like you're doing;
where do you see which mode I am running in? I havent specified any
modes in
[EMAIL PROTECTED] wrote:
Is there some kind of limit in postgresql about the number of partitions ? Do
you know some tuning in the conf files to improve postgresql management of so
many tables ? I have already used different tablespaces, one for each main table
and its 288 partitions.
Postgre
force inode syncing (dont rememver the name for the filesystem
structure in unix memory). The same question can be asked about the
async option.
any thoughts?
thomas
Greg Smith wrote:
On Tue, 2 Sep 2008, Thomas Finneid wrote:
You should try setting this to open_sync , that can be considerably
Greg Smith wrote:
On Mon, 1 Sep 2008, Thomas Finneid wrote:
Thanks for all the info on the disk controller, I will have to look
through all that now :)
I note that nobody has talked about your postgresql.conf yet. I assume
you've turned autovacuum off because you're not eve
Scott Carey wrote:
For a development box, just enable write-back caching regardless of the
battery back up situation. As long as its not your only copy of
Will have a look at it, the data is not important and can be reproduced
any time on any machine. The controller I have is a Areca ARC-12
Craig Ringer wrote:
Just on a side note, your system is pretty strangely heavy on CPU
compared to its RAM and disk configuration. Unless your workload in Pg
is computationally intensive or you have something else hosted on the
same machine, those CPUs will probably sit mostly idle.
Its a devel
Scott Carey wrote:
Are you even getting COPY to work with JDBC? As far as I am aware, COPY
doesn't work with JDBC at the moment:
I used a patched jdbc driver, provided by someone on the list, dont have
the reference at hand. It works perfectly and its about 5 times faster,
for my job, than
Scott Carey wrote:
You may want to investigate pg_bulkload.
http://pgbulkload.projects.postgresql.org/
One major enhancement over COPY is that it does an index merge, rather
than modify the index one row at a time.
This is a command line tool, right? I need a jdbc driver tool, is that
pos
Hi
I am working on a table which stores up to 125K rows per second and I
find that the inserts are a little bit slow. The insert is in reality a
COPY of a chunk of rows, up to 125K. A COPY og 25K rows, without an
index, is fast enough, about 150ms. With the index, the insert takes
about 500ms
Hi
I couldnt find any specifics on this subject in the documentation, so I
thought I'd ask the group.
how does pg utilise multi cpus/cores, i.e. does it use more than one
core? and possibly, how, are there any documentation about this.
thomas
---(end of broadcast)--
Kalle Hallivuori wrote:
Hi.
2007/7/8, Thomas Finneid <[EMAIL PROTECTED]>:
Kalle Hallivuori wrote:
> COPY is plentitudes faster than INSERT:
> http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
> If you can't just push the data straight into the final t
Michael Stone wrote:
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar
to what happens in there.
No, because it only works on file to db or vice versa not table to table.
I
Michael Glaesemann wrote:
As they're individual inserts, I think what you're seeing is overhead
from calling this statement 100,000 times, not just on the server but
also the overhead through JDBC. For comparison, try
CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;
INSERT INTO ciu_data_
PFC wrote:
Unfortunately its not fast enough, it needs to be done in no more than
1-2 seconds, ( and in production it will be maybe 20-50 columns of
data, perhaps divided over 5-10 tables.)
Additionally it needs to scale to perhaps three times as many columns
and perhaps 2 - 3 times as many ro
Hi
During the somes I did I noticed that it does not necessarily seem to be
true that one needs the fastest disks to have a pg system that is fast.
It seems to me that its more important to:
- choose the correct methods to use for the operation
- tune the pg memory settings
- tune/disable pg x
PFC wrote:
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took
14 seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on
Mark Lewis wrote:
If you're performing via JDBC, are you using addBatch/executeBatch, or
are you directly executing each insert? If you directly execute each
insert, then your code will wait for a server round-trip between each
insert.
I tested both and I found almost no difference in the ti
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
No, because it only works on file to db or vice versa not table to table.
regards
thoams
---(end of broadcast)---
TIP
Tom Lane wrote:
Michael Glaesemann <[EMAIL PROTECTED]> writes:
It would be helpful if you included the actual queries you're using,
as there are a number of variables:
Not to mention which PG version he's testing.
Its pg 8.1, for now, I'll be upgrading to a compile optimised 8.2 when I
Michael Glaesemann wrote:
On Jul 17, 2007, at 14:38 , Thomas Finneid wrote:
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took
14 seconds, compared to a select into, which took 0.
Hi
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took 14
seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on the local m
Kalle Hallivuori wrote:
> COPY is plentitudes faster than INSERT:
> http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
> If you can't just push the data straight into the final table with
> COPY, push it into a temporary table that you go through with the
> database procedure.
>
> Sh
Heikki Linnakangas wrote:
ISTM that a properly normalized schema would look something like this:
create table position (
posX int not null,
posY int not null,
primary key (posX, posY)
);
create table colour (
posX int not null,
posY int not null,
colour varchar(50) not null,
prim
42 matches
Mail list logo
