On 06/10/2017 07:32 PM, Alvaro Herrera wrote:
> Frits Jalvingh wrote:
>
>> So, I am still very interested in getting normal inserts faster, because
>> that will gain speed for all work.. If Oracle can do it, and Postgres is
>> able to insert fast with copy- where lies the bottleneck with the inser
aggregate
plans, but I suspect there is a lot to be gained there as well.
HTH,
Joe
- --
Joe Conway
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)
iQIcBAEBAgAGBQJVfHITAAoJEDfy90M199hlGvcP/ijyCsXnWZAeZSUAW4qb20YJ
AHKn0Gl8D9mH9cfPfJeCO+60dcWINzUE6l7qOWWN8JtT6pgbRPGvQsCkx9xRz
manage so many databases, but I
would test it carefully before committing.
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)
Comm
On 11/21/2012 09:28 AM, Craig James wrote:
>
>
> On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway <mailto:m...@joeconway.com>> wrote:
>
> On 11/21/2012 08:05 AM, Heikki Linnakangas wrote:
> > Rather than telling the planner what to do or not to do, I'd m
after sprinkling your
SQL with hints, you could easily periodically (e.g. after a Postgres
upgrade) test what would happen if the hints were removed.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x
less clear as concurrency was increased.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
signature.asc
Description: OpenPGP digital signature
ere id in (42, 4242, 424242);
explain analyze select * from testbit where status & 512 = 512;
QUERY PLAN
--
Index Scan using idx32 on testbit (cost=0.00..4712.62 rows=5000
width=22) (actual time=0.080..0.085 rows=3
there are also some good examples of array handling in PL/R, e.g.
pg_array_get_r() in pg_conversion.c
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support
signature.asc
Description: OpenPGP digital signature
On 06/10/2010 01:21 PM, Anne Rosset wrote:
>>
> I tried that and it didn't make any difference. Same query plan.
A little experimentation suggests this might work:
create index item_rank_project on item_rank(project_id, rank) where
pf_id IS NULL;
Joe
signature.asc
Description: OpenPGP digi
On 06/10/2010 01:10 PM, Joe Conway wrote:
> try:
>
> create index item_rank_null_idx on item_rank(pf_id)
> where rank IS NOT NULL AND pf_id IS NULL;
oops -- that probably should be:
create index item_rank_null_idx on item_rank(project_id)
where rank IS NOT NULL AND pf_id I
On 06/10/2010 12:56 PM, Anne Rosset wrote:
> Craig James wrote:
>> create index item_rank_null_idx on item_rank(pf_id)
>>where item_rank.pf_id is null;
>>
>> Craig
>>
> Hi Craig,
> I tried again after adding your suggested index but I didn't see any
> improvements: (seems that the index is no
On 02/22/2010 07:01 PM, Dave Crooke wrote:
> The original data is keyed by a 3-ary tuple of strings to keep the
> row size down, in the new data model I'm actually storing 32-bit int's
> in Postgres. The new schema for each table looks like this:
>
> (a integer,
> b integer,
> c integer,
>
Hartman, Matthew wrote:
I'm pretty much at that point where I've chewed the fat off of the
algorithm, or at least at my personal limits. Occasionally a new idea
pops into my head and yields an improvement but it's in the order of
100-250ms.
Google came back with "no sir". It seems PostgreSQL is
Jeremiah Elliott wrote:
ok, I have an application that I am trying to speed up. Its a reporting
application that makes heavy use of the crosstab function.
here is an example query
The crostab function is taking between 5 and 15 seconds to return.
Please run the two embedded queries ind
Stephen Frost wrote:
* Guoping Zhang ([EMAIL PROTECTED]) wrote:
Obviously, if there is no better solution, the TCP round trip penalty will
stop us doing so as we do have performance requirement.
Actually, can't you stick multiple inserts into a given 'statement'?
ie: insert into abc (123); in
Gabriele Turchi wrote:
Il giorno sab, 15/07/2006 alle 13.04 -0700, Joe Conway ha scritto:
Why not just periodically (once an hour?) run "ANALYZE registrazioni;"
during the day. This will only update the statistics, and should be very
low impact.
This is my "solution" t
Gabriele Turchi wrote:
Running an ANALYZE really change the plan, now it is fast as before
(8.0).
On the production system a VACUUM FULL ANALYZE is run every morning
after a clean-up, when the "registrazioni" table is empty. During the
day this table fills up (about 500 record any day), and appa
Dan Gorman wrote:
That makes sense. Speaking of NetApp, we're using the 3050C with 4 FC
shelfs. Any generic advice other than the NetApp (their NFS oracle
tuning options)
that might be useful? (e.g. turning off snapshots)
I'm not sure if this is in the tuning advice you already have, but we
Jim C. Nasby wrote:
On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote:
Hi,
We've got a C function that we use here and we find that for every
connection, the first run of the function is much slower than any
subsequent runs. ( 50ms compared to 8ms)
Besides using connection pooling
Aditya wrote:
On Mon, Apr 11, 2005 at 10:59:51AM -0700, Joe Conway wrote:
Any particular reason? Our NetApp technical rep advised nfs over iSCSI,
IIRC because of performance.
I would mount the Netapp volume(s) as a block level device on my server
using
iSCSI (vs. a file-based device like NFS) so
Aditya wrote:
We have not, AFAICT, had any problems with the traffic over NFS as far as
reliability -- I'm sure there is a performance penalty, but the reliability
and scalability gains more than offset that.
My experience agrees with yours. However we did find one gotcha -- see
the thread startin
Alexandre Leclerc wrote:
I'm a little bit confused on how to install this contirb. I know my
contrib package is installed, but I don't know how to make it work in
postgresql. (Using 7.4.5-1mdk on Mandrake Linux.)
Find the file tablefunc.sql and redirect it into your database, e.g.
psql mydatabase <
Alexandre Leclerc wrote:
Here a normal listing of design.product_department_time:
product_id | department_id | req_time
+---+--
906 | A | 3000
906 | C | 3000
906 | D | 1935
907 | A
Alex Turner wrote:
I appreciate your information, but it's not valid. Most people don't
need RAC or table partitioning.
From a small company perspective, maybe, but not in the least invalid
for larger companies.
Many of the features in Oracle EE are just not available in Postgresql at all,
and
Alex Turner wrote:
I'm not advocating that people switch to Oracle at all, It's still
much more expensive than Postgresql, and for most small and medium
applications Postgresql is much easier to manage and maintain. I
would just like to make sure people get their facts straight. I
worked for a co
Greg Sabino Mullane wrote:
Don't forget your support contract cost, as well as licenses for each
of your servers: development, testing, QA, etc.
Is it really as "cheap" as 5K? I've heard that for any fairly modern
system, it's much more, but that may be wrong.
Sort of -- see:
http://oraclestore
Spiegelberg, Greg wrote:
My experience with dblink() is that each dblink() is executed serially
Correct.
If you really want to do multiple queries simultaneously, you would need
to write a function very similar to dblink_record, but using asynchonous
libpq calls to both remote hosts. See:
http:
Josh Berkus wrote:
Thing is, some companies are required to use 1st-tier or at least 2nd-tier
vendors for hardware; they won't home-build. For those people, what vendors
do others on this list recommend? What have been your good/bad experiences?
I've had very good experiences with IBM hardwar
Iain wrote:
Joe's example wasn't excluding partions, as he didn't use a predicated UNION
ALL view to select from. His queries use an indexed column that allow the
various partitions to be probed at low cost, and he was satisfied wth that.
Right.
My point in my previous post was that you could still
Christopher Browne wrote:
In the last exciting episode, [EMAIL PROTECTED] (Joe Conway) wrote:
That's exactly what we're doing, but using inherited tables instead of
a union view. With inheritance, there is no need to rebuild the view
each time a table is added or removed. Basical
Iain wrote:
That's exactly what we're doing, but using inherited tables instead of a
union view. With inheritance, there is no need to rebuild the view each
time a table is added or removed. Basically, in our application, tables
are partitioned by either month or week, depending on the type of data
Simon Riggs wrote:
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
I think
Josh Berkus wrote:
- the use of inherited tables to partition this huge number of rows and
yet allow simple query access to it seems to work well, at least in
early validation tests
- had we simply taken the original database and "slammed" it into
Postgres with no further thought, we wou
[EMAIL PROTECTED] wrote:
Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24:
We're not completely done with our data conversion (from a commercial
RDBMSi), but so far the results have been excellent. Similar to what
others have said in this thread, the conversion involved re
Chris Browne wrote:
Might we set up the view as:
create view combination_of_logs as
select * from table_1 where txn_date between 'this' and 'that'
union all
select * from table_2 where txn_date between 'this2' and 'that2'
union all
select * from table_3 where txn_date between 'this3'
[EMAIL PROTECTED] wrote:
Would NAS or SAN be good solutions ? (I've read that NAS uses NFS which
could slow down the transfer rate ??)
Has anyone ever tried one of these with postgresql ?
Not (yet) with Postgres, but my company has run ~100GB Oracle database
on NAS (NetApp) for the past couple
Bill wrote:
Ok, so maybe someone on this group will have a better idea. We have a
database of financial information, and this has literally millions of
entries. I have installed indicies, but for the rather computationally
demanding processes we like to use, like a select query to find the
commod
Sean Shanny wrote:
explain analyze SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER
JOIN d_referral t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id;
What I would like to know is if there are better ways to do the join? I
need to get all the rows back from the referral_temp table as they
Joe Conway wrote:
In isolation, test_run.sql should do essentially no syscalls at all once
it's past the initial ramp-up. On a machine that's functioning per
expectations, multiple copies of test_run show a relatively low rate of
semop() calls --- a few per second, at most --- a
Tom Lane wrote:
Here is a test case. To set up, run the "test_setup.sql" script once;
then launch two copies of the "test_run.sql" script. (For those of
you with more than two CPUs, see whether you need one per CPU to make
trouble, or whether two test_runs are enough.) Check that you get a
nestl
scott.marlowe wrote:
On Mon, 19 Apr 2004, Bruce Momjian wrote:
I have BSD on a SuperMicro dual Xeon, so if folks want another
hardware/OS combination to test, I can give out logins to my machine.
I can probably do some nighttime testing on a dual 2800MHz non-MP Xeon
machine as well. It's a Dell 2
Dirk Lutzebäck wrote:
Joe, do you know where I should look in the 7.4.2 code to find this out?
I think I was wrong. I just looked in CVS and found the commit I was
thinking about:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/lmgr/s_lock.c.diff?r1=1.22&r2=1.23
http:/
Dirk Lutzebäck wrote:
postgresql 7.4.1
a new Dual Xeon MP
too much context switches (way more than 100.000) on higher load (meaning system
load > 2).
I believe this was fixed in 7.4.2, although I can't seem to find it in
the release notes.
Joe
---(end of broadcast)
Jeff wrote:
I think it was on this list - someone posted a message about SETOF
being slower. Tom replied saying it was because it needed to create an
on-disk tuplestore.
I was just looking for some clarification - a SETOF function will always
write the reslting tuples to disk (Not buffering i
Arthur Ward wrote:
Jan's vacuum-delay-only patch that nobody can find is here:
http://archives.postgresql.org/pgsql-hackers/2003-11/msg00518.php
I've been using it in testing & production without any problems.
Great to know -- many thanks.
I've hacked my own vacuum-delay-only patch form Jan's al
Andrew Sullivan wrote:
Sorry I haven't had a chance to reply to this sooner.
The vacuum delay stuff that you're working on may help, but I can't
really believe it's your salvation if this is happening after only a
few minutes. No matter how much you're doing inside those functions,
you surely can
Matthew T. O'Connor wrote:
If memory serves, the problem is that you actually sleep 10ms even when
you set it to 1. One of the thing changed in Jan's later patch was the
ability to specify how many pages to work on before sleeping, rather
than how long to sleep inbetween every 1 page. You might b
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
Any idea where I can get my hands on the latest version. I found the
original post from Tom, but I thought there was a later version with
both number of pages and time to sleep as knobs.
That was as far as I got. I think Jan posted
Matthew T. O'Connor wrote:
Strange... I wonder if this is some integer overflow problem. There was
one reported recently and fixed as of CVS head yesterday, you might try
that, however without the -d2 output I'm only guessing at why
pg_autovacuum is vacuuming so much / so often.
I'll see what I
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
Any idea where I can get my hands on the latest version. I found the
original post from Tom, but I thought there was a later version with
both number of pages and time to sleep as knobs.
That was as far as I got. I think Jan posted
Matthew T. O'Connor wrote:
I think you understand correctly. A table with 1,000,000 rows should
get vacuumed approx every 2,000,000 changes (assuming default values for
-V ). FYI and insert and a delete count as one change, but and update
counts as two.
Unfortunately, the running with -d2 wou
Marty Scholes wrote:
I have seen similar results to what you are describing.
I found that running a full vacuum:
vacuumdb -fza
followed by a checkpoint makes it run fast again.
Try timing the update with and without a full vacuum.
Will do. I'll let you know how it goes.
Thanks for the reply.
I'm trying to troubleshoot a performance issue on an application ported
from Oracle to postgres. Now, I know the best way to get help is to post
the schema, explain analyze output, etc, etc -- unfortunately I can't do
that at the moment. However, maybe someone can point me in the right
directio
Joshua D. Drake wrote:
With the new preload option is there any benefit/drawback to using
pl/Python versus pl/pgSQL?
If you're asking about relative speed, I did some *very* simple tests
and posted them here:
http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php
without preload:
===
William Yu wrote:
My situation is this. We have a semi-production server where we
pre-process data and then upload the finished data to our production
servers. We need the fastest possible write performance. Having the DB
go corrupt due to power loss/OS crash is acceptable because we can
always
David Griffiths wrote:
Yes, the query operates only on indexed columns (all numeric(10)'s).
Column |Type |
Modifiers
---+-+--
---
user_account_id | numeric
David Griffiths wrote:
I think you want something like:
UPDATE user_account SET last_name = 'abc'
WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
WHERE user_account.user_account_id = ce.user_account_id AND
ce.commercial_entity_id = cs.commercial_entity_id);
Unfort, this is s
Greg Spiegelberg wrote:
The reason for my initial question was this. We save changes only.
In other words, if system S has row T1 for day D1 and if on day D2
we have another row T1 (excluding our time column) we don't want
to save it.
It still isn't entirely clear to me what you are trying to do,
Scott Cain wrote:
Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is
109M.
Thanks. I'll grab a copy from home later today and see if I can find
some time to poke at it.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscr
Tom Lane wrote:
Scott Cain <[EMAIL PROTECTED]> writes:
A few days ago, I asked for advice on speeding up substring queries on
the GENERAL mailing list. Joe Conway helpfully pointed out the ALTER
TABLE STORAGE EXTERNAL documentation. After doing the alter,
the queries got slower! Here
Medora Schauer wrote:
I would greatly appreciate it if someone could run this code in their
environment and let me know if you get results similiar to mine.
The INT test results in execution times of 11 - 50+ secs increasing
each time the test is run. The FLOAT test execution times are
consistentl
Scott Cain wrote:
On Mon, 2003-08-04 at 11:53, Tom Lane wrote:
I find it really, really hard to believe that a crude reimplementation
in plpgsql of the TOAST concept could beat the built-in implementation
at all, let alone beat it by two orders of magnitude.
Either there's something unrealistic abo
Scott Cain wrote:
I am not against recompiling. I am currently using an RPM version, but
I could probably recompile; the compilation is probably straight forward
(adding something like `--with_profiling` to ./configure), but how
straight forward is actually doing the profiling? Is there a documen
Scott Cain wrote:
So it is possible that if I had a fast scsi drive, the performance might
be better?
Faster drives are always better ;-)
Did you try the comparison with shorter substrings? Also, maybe not
related to your specific question, but have you tuned any other
postgresql.conf settings?
Scott Cain wrote:
Index Scan using feature_pkey on feature (cost=0.00..3.01 rows=1
width=153) (actual time=954.13..954.14 rows=1 loops=1)
Index Cond: (feature_id = 1)
Total runtime: 954.26 msec
(3 rows)
Whoa! That's not what I expected, the time to do the query got more
that twice as long.
Castle, Lindsay wrote:
I'm working on a project that has a data set of approximately 6million rows
with about 12,000 different elements, each element has 7 columns of data.
I'm wondering what would be faster from a scanning perspective (SELECT
statements with some calculations) for this type of set
Castle, Lindsay wrote:
The data structure looks like this:
element
date
num1
num2
num3
num4
units
There are approx 12,000 distinct elements for a total of about 6 million
rows of data.
Ahh, that helps! So are the elements evenly distributed, i
Nick Fankhauser wrote:
Thanks for the correction- it sounds like this is one where usage
can't be precisely controlled in a dynamic user environment & I just
need to get a feel for what works under a load that approximates my
production system.
I think the most important point here is that if you s
Adam Witney wrote:
Actually I am going through the same questions myself at the moment I
would like to have a 2 disk RAID1 and a 4 disk RAID5, so need at least 6
disks
Anybody have any suggestions or experience with other hardware manufacturers
for this size of setup? (2U rack, up to 6 disk
Jean-Luc Lachance wrote:
I am currious. How can you have RAID 1+0 with only 2 drives?
If you are thinking about partitioning the drives, wont this defeate the
purpose?
Yeah -- Hannu already pointed out that my mind was fuzzy when I made
that statement :-(. See subsequent posts.
Joe
---
Adam Witney wrote:
I think the issue from the original posters point of view is that the Dell
PE2650 can only hold a maximum of 5 internal drives
True enough, but maybe that's a reason to be looking at other
alternatives. I think he said the hardware hasn't been bought yet.
Joe
---
Hannu Krosing wrote:
How do you do RAID 1+0 with just two drives ?
Hmm, good point -- I must have been tired last night ;-). With two
drives you can do mirroring or striping, but not both.
Usually I've seen a pair of mirrored drives for the OS, and a RAID 1+0
array for data. But that requires 6
Balazs Wellisch wrote:
first of all I'd like to thank everyone who responded to my earlier
post. I have a much better understanding of postgres performance
tuning now. In case anyone's interested we've decided to go with RH9
and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves.
(should be
Balazs Wellisch wrote:
I would *not* use the default version of Postgres shipped with any
particular distribution. Use 7.3.3 because that is the latest released
version. Or, as Shridhar mentioned in his post, the are a number of
pretty significant performance improvements in 7.4 (which is in featur
Balazs Wellisch wrote:
I don't have months to learn the ins and outs of PostgreSQL
performance tuning so I looked around to see if there are any
preconfigured solutions out there.
I don't know of a preconfigured solution. Generally speaking, the best
configuration will be highly dependent on your
75 matches
Mail list logo
