iently query table A? Or, in other words, how do I
avoid incurring the cost of transaction isolation for queries against B on
a case-by-case basis?
Anything is on the table for implementation:
- moving tables to a different database / cluster / completely different
DBMS system
- designing an extension to tune either sets of queries
- partitioning tables
- etc
... although the simpler the better. If you were in this position, what
would you do?
Regards,
James
This is a weird problem. A "limit 5" query runs quicky as expected, but a
"limit 1" query never finishes -- it just blasts along at 100% CPU until I
give up. And this is a join between two small tables (262K rows and 109K
rows). Both tables were recently analyzed.
This is Postgres 9.3.5 (yes, we'l
lose to 6,000 records/second. The schema has been
> simplified since and last test reach just over 20,000 records/second with
> 300k tables.
>
> I'm not looking for alternatives yet but input to my test. Takers?
>
> I can't promise immediate feedback but will do my best to respond with
> results.
>
> TIA,
> -Greg
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
le! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
version to see if it speeds up things.
>
> However is there way to keep query time constant as the database size
> grows. Should I use partitioning or partial indexes?
>
> Best Regards,
> Tommi Kaksonen
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
On Thu, Jun 23, 2016 at 8:47 AM, Jeff Janes wrote:
> On Wed, Jun 22, 2016 at 9:36 PM, Craig James
> wrote:
> > On Wed, Jun 22, 2016 at 11:36 AM, Jeff Janes
> wrote:
> ...
> But, JChem's cartridge is apparently not using a GiST index, which is
> what my first gues
On Wed, Jun 22, 2016 at 11:36 AM, Jeff Janes wrote:
> On Wed, Jun 22, 2016 at 9:03 AM, Craig James
> wrote:
> > I'm working with a third-party plugin that does chemistry.
>
>
> Out of personal/professional curiosity, which one are you using, if
> that can be
I'm working with a third-party plugin that does chemistry. It's very fast.
However, I'm trying to do a sampling query, such as the first 1% of the
database, and I just can't get the planner to create a good plan. Here is
the full query (the |>| operator does a subgraph match of a molecular
substru
On Sun, Feb 28, 2016 at 2:06 AM, David Rowley
wrote:
> On 27 February 2016 at 11:07, James Parks wrote:
> >
> > CREATE TABLE a (id bigint primary key, nonce bigint);
> > CREATE TABLE b (id bigint primary key, a_id bigint not null);
> > CREATE INDEX a_idx ON
eing
compared to a non-negligible number of rows in (a).
I can probably make this data available as a pg_dump file. Let me know if
you think that's necessary, and where I should upload it.
Regards,
James
[1]
postgres=# explain (analyze,buffers) select b.* from b join a on b.
At some point in the next year we're going to reconsider our hosting
environment, currently consisting of several medium-sized servers (2x4
CPUs, 48GB RAM, 12-disk RAID system with 8 in RAID 10 and 2 in RAID 1 for
WAL). We use barman to keep a hot standby and an archive.
The last time we dug into
rmance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
On Sat, Jul 25, 2015 at 8:04 AM, Tom Lane wrote:
> Craig James writes:
> > ... This would result in a thousand
> > or so Postgres connections on a machine with 32 CPUs.
>
> > So the question is: do idle connections impact performance?
>
> Yes. Those connec
The canonical advice here is to avoid more connections than you have CPUs,
and to use something like pg_pooler to achieve that under heavy load.
We are considering using the Apache mod_perl "fast-CGI" system and perl's
Apache::DBI module, which caches persistent connections in order to improve
per
B> scaling will suffer badly on FP functions.
That is half as many 256-bit float units; for scalar math and for
128-bit vector math each core gets a half of the float unit.
Only for the 256-bit vector math do the schedulars have to compete for
float unit access.
-JimC
--
James Cloos
On Wed, Jul 8, 2015 at 1:27 PM, Andres Freund wrote:
> On 2015-07-08 13:46:53 -0500, Merlin Moncure wrote:
> > On Wed, Jul 8, 2015 at 12:48 PM, Craig James
> wrote:
> > > On Tue, Jul 7, 2015 at 10:31 PM, Joshua D. Drake >
> > >> Using Apache Fast-CGI, you
On Wed, Jul 8, 2015 at 10:52 AM, Joshua D. Drake
wrote:
>
> On 07/08/2015 10:48 AM, Craig James wrote:
>
> I admit that I haven't read this whole thread but:
>>
>> Using Apache Fast-CGI, you are going to fork a process for each
>> instance of the
On Tue, Jul 7, 2015 at 10:31 PM, Joshua D. Drake
wrote:
>
> On 07/07/2015 08:05 PM, Craig James wrote:
>
>>
>>
>> No ideas, but I ran into the same thing. I have a set of C/C++ functions
>> that put some chemistry calculations into Postgres as extensions (things
ll 100% of all eight CPUs.
I have no idea why, and never investigated further. The convenience of
having the functions in SQL wasn't that important.
Craig
>
> Graeme.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
On Wed, Jul 1, 2015 at 4:57 PM, Scott Marlowe
wrote:
> On Wed, Jul 1, 2015 at 5:06 PM, Craig James wrote:
> > We're buying a new server in the near future to replace an aging system.
> I'd
> > appreciate advice on the best SSD devices and RAID controll
On Wed, Jul 1, 2015 at 4:56 PM, Andreas Joseph Krogh
wrote:
> På torsdag 02. juli 2015 kl. 01:06:57, skrev Craig James <
> cja...@emolecules.com>:
>
> We're buying a new server in the near future to replace an aging system.
> I'd appreciate advice on the best
.@postgresql.org] *On Behalf Of *Andreas Joseph
> Krogh
> *Sent:* Wednesday, July 01, 2015 6:56 PM
> *To:* pgsql-performance@postgresql.org
> *Subject:* Re: [PERFORM] New server: SSD/RAID recommendations?
>
>
>
> På torsdag 02. juli 2015 kl. 01:06:57, skrev Craig James <
We're buying a new server in the near future to replace an aging system.
I'd appreciate advice on the best SSD devices and RAID controller cards
available today.
The database is about 750 GB. This is a "warehouse" server. We load
supplier catalogs throughout a typical work week, then on the weeken
On Fri, Oct 10, 2014 at 9:53 AM, Tomas Vondra wrote:
>
> On 10.10.2014 16:21, Craig James wrote:
> > Our index is for chemical structures. Chemicals are indexed on
> > chemical fragments
> > <http://emolecules.com/info/molecular-informatics>. A search
> >
On Fri, Oct 10, 2014 at 5:10 AM, Tomas Vondra wrote:
> > I've gone looking for papers on this topic but from what I read this
> > isn't so. To get any noticeable improvement you need to read 10-50% of
> > the table and that's effectively the same as reading the entire table
> > -- and it still ha
Hi Jiri,
I’m really interested in those [clustering] algorithms and study them. But
> I would need somebody to point me directly at a specific algorithm to look
> at. The main problem with choosing the right one (which couldn’t get over
> even my university teacher) is that you don’t know the numb
, but anyway.
>
> Could you first apply a kind of grid to your observations? What I mean is
> to round your coords to, say, 1/2 arcsec on each axe and group the results.
> I think you will have most observations grouped this way and then use your
> regular algorithm to combine the results.
>
> Best regards, Vitalii Tymchyshyn
>
--
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-
scan may not be as
efficient since 4 times the data returned in just a little more time using the
index scan.
Thank you,
James
-Original Message-
From: Tomas Vondra [mailto:t...@fuzzy.cz]
Sent: Monday, July 14, 2014 12:29 PM
To: Magers, James
Cc: Tomas Vondra; Thomas Kellerer; pgsql
processes running against the
database.
My assessment is based on my experiences with the scans. Does your experience
provide you with a different assessment of the scan types and how efficient
they may be?
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance
issue in trying to isolate if
the query can be faster is that once the data is cached any way that the query
is executed appears to be quicker.
http://explain.depesz.com/s/SIX1
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
://explain.depesz.com/s/GfM
bitmapscan off: http://explain.depesz.com/s/3wna
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Tomas,
Thank you for your feedback. I am attaching the requested information. While
I do not think the query is necessarily inefficient, I believe a sequence scan
would be more efficient.
\d member_subscription_d
Table "public.member_subscription_d"
I am using a Pentaho process to access the database and select the appropriate
information to update the DB tables and records. I am trying to select the
previous subscription key in order to update the factable for any records that
have the previous key to have the current subscription key. Th
Day and night, the postgres stats collector process runs at about 20 MB/sec
output. vmstat shows this:
$ vmstat 2
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us sy id
wa
0 0 55864 135740 123804
On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph Krogh wrote:
> I have a schema where I have lots of messages and some users who might
> have read some of them. When a message is read by a user I create an entry
> i a table message_property holding the property (is_read) for that user.
>
> The schem
Shaun Thomas wrote:
>
>> these issues tend to get solved through optimization fences.
>>> Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick.
>>> How are these nothing other than unofficial hints?
>>>
>> Yeah, the cognitive dissonance levels get pretty high around this
>> issue. S
There have been many discussions about adding hints to Postgres over the
years. All have been firmly rejected by the Postgres developers, with
well-argued reasons. Search the archives to learn more about this topic.
On the other hand, Postgres does have hints. They're just called settings.
You c
On Sun, Dec 15, 2013 at 8:18 AM, Yuri Levinsky wrote:
> Dear ALL,
>
> I am running PL/pgsql procedure with sql statements that taking a long
> time. I able to see them in the log just after their completion. How can I
> see currently running SQL statement? I am able to see in pg_stat_activity
>
On Wed, Dec 11, 2013 at 2:29 PM, Janek Sendrowski wrote:
> Hi,
>
> How can I use this ORDER BY using index feature presented in this
> implementation.
> It doesn't seem to be in use, when I have a look in my query plan.
> It still does an cost intensive Bitmap Heap Scan and a Bitmap Index scan.
>
On Tue, Nov 26, 2013 at 10:40 AM, Ben Chobot wrote:
> On Nov 26, 2013, at 9:24 AM, Craig James wrote:
>
> So far I'm impressed by what I've read about Amazon's Postgres instances.
> Maybe the reality will be disappointing, but (for example) the idea of
> setting up
On Tue, Nov 26, 2013 at 8:29 AM, David Boreham wrote:
> On 11/26/2013 7:26 AM, Craig James wrote:
>
>>
>> For those of us with small (a few to a dozen servers), we'd like to get
>> out of server maintenance completely. Can anyone with experience on a cloud
>> VM
>
> On 25.11.2013 22:01, Lee Nguyen wrote:
>>
>>>
>>> Why shouldn't we run Postgres in a VM? What are the downsides? Does
>>> anyone
>>> have any metrics or benchmarks with the latest Postgres?
>>>
>>
For those of us with small (a few to a dozen servers), we'd like to get out
of server maintenance
2013/11/18 Rogerio Pereira
> Which can be the error :
>
> -- could not send data to client: Broken pipe
> -- FATAL: connection to client lost
>
It means the client program disconnected from the Postgres server (or was
killed) before the server finished a query, and the server had no place to
se
On Sun, Sep 29, 2013 at 2:24 PM, Ken Tanzer wrote:
> p.s., This script runs fine on my computer (Ubuntu 13.04), but on a
>>> Fedora 11 machine it dies with
>>>
>>> pg_analyze_info.sh: line 18: unexpected EOF while looking for matching
>>> `)'
>>> pg_analyze_info.sh: line 57: syntax error: unexp
On Sun, Sep 29, 2013 at 2:09 AM, Ken Tanzer wrote:
> I just sent off to this list for query help, and found the process of
> gathering all the requested info somewhat tedious. So I created a little
> BASH script to try to pull together as much of this information as
> possible.
>
> The script re
On Sat, Sep 14, 2013 at 11:36 AM, bricklen wrote:
> On Sat, Sep 14, 2013 at 11:28 AM, Craig James wrote:
>
>> I'm trying to do a pg_dump of a database, and it more-or-less just sits
>> there doing nothing.
>>
>
> What is running in the db? Perhaps there is som
I'm trying to do a pg_dump of a database, and it more-or-less just sits
there doing nothing. "vmstat 2" looked like this during pg_dump:
procs ---memory-- ---swap-- -io -system--
cpu
r b swpdfree buff cachesi sobibo in cs us sy
id wa
On Sat, Aug 31, 2013 at 10:06 AM, Łukasz Walkowski <
lukasz.walkow...@homplex.pl> wrote:
> > I think the main "pro" of this approach is that it doesn't use any
> > nonstandard SQL features, so you preserve your options to move to some
> > other database in the future. The main "con" is that you'd
On 23/05/2013 22:57, Jonathan Morra wrote:
I'm not sure I understand your proposed solution. There is also the
case to consider where the same patient can be assigned the same
device multiple times. In this case, the value may be reset at each
assignment (hence the line value - issued_value A
On Wed, May 15, 2013 at 1:15 PM, Gavin Flower wrote:
> On 16/05/13 04:23, Craig James wrote:
>
> On Wed, May 15, 2013 at 8:31 AM, Shaun Thomas wrote:
>
>> [Inefficient plans for correlated columns] has been a pain point for
>> quite a while. While we've had severa
On Wed, May 15, 2013 at 8:31 AM, Shaun Thomas wrote:
> [Inefficient plans for correlated columns] has been a pain point for quite
> a while. While we've had several discussions in the area, it always seems
> to just kinda trail off and eventually vanish every time it comes up.
>
> ...
> Since ther
On Wed, May 1, 2013 at 5:18 PM, Tom Lane wrote:
> Craig James writes:
> > I have two tables that are nearly identical, yet the same query runs 100x
> > slower on the newer one. ...
>
> > db=> explain analyze select id, 1 from str_conntab
> > where (id >= 1200
I have two tables that are nearly identical, yet the same query runs 100x
slower on the newer one. The two tables have the same number of rows (+/-
about 1%), and are roughly the same size:
db=> SELECT relname AS table_name,
db-> pg_size_pretty(pg_relation_size(oid)) AS table_size,
db-> pg_size_p
On Fri, Mar 1, 2013 at 1:52 AM, Steven Crandell
wrote:
> Recently I moved my ~600G / ~15K TPS database from a
> 48 core@2.0GHz server with 512GB RAM on 15K RPM disk
> to a newer server with
> 64 core@2.2Ghz server with 1T of RAM on 15K RPM disks
>
> The move was from v9.1.4 to v9.1.8 (eventually a
about. Dell basically doesn't understand Postgres.
They boast excellent on-site service, but for the price of their computers
and their service contract, you can buy two servers from a white-box
vendor. Our white-box servers have been just as reliable as the Dell
servers -- no failures.
If I drop and then recreate a trigger inside of a single transaction, how
does it affect other processes trying to use the same table? Can they just
merrily go along their way using the table, or will they be blocked by an
exclusive lock?
We have a trigger that detects illegal drugs and dangerous
Le 2013-01-07 à 16:49, james a écrit :
Is there a way to force a WAL flush so that async commits (from other
connections) are flushed, short of actually updating a sacrificial row?
Would be nice to do it without generating anything extra, even if it is
something that causes IO in the
Is there a way to force a WAL flush so that async commits (from other
connections) are flushed, short of actually updating a sacrificial row?
Would be nice to do it without generating anything extra, even if it is
something that causes IO in the checkpoint.
Am I right to think that an empty t
On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway 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 much rather
> > have hints that give the planner more information about the tables and
> > quals involved in the query. A typical
vice. The correct hint
syntax would be suggested only when all other avenues failed.
Craig James
>
> -Kevin
>
rly an important need, given the nature of the dialog above (and
many others that have passed through this mailing list).
Why not make an explicit hint syntax and document it? I've still don't
understand why "hint" is a dirty word in Postgres. There are a half-dozen
or so ways i
... did you
do an "analyze" on the small table? I've been hit by this before ... it's
natural to think that Postgres would always check a very small table first
no matter what the statistics are. But it's not true. If you analyze the
small table, even if it only has one or two rows in it, it will often
radically change the plan that Postgres chooses.
Craig James
ms of sequential input whether or not cache is enabled
>>> on
>>> the
>>> RAID1 (SAS 15K, sdb).
>
>
> Maybe there's a misunderstanding here.. :) Craig (James) is the one
> the had started this thread. I've joined later suggesting a way to
> disable HT
On Mon, Oct 15, 2012 at 1:27 AM, Andrea Suisani wrote:
> On 10/11/2012 04:40 PM, Andrea Suisani wrote:
>>
>> On 10/11/2012 04:19 PM, Claudio Freire wrote:
>>>
>>> On Thu, Oct 11, 2012 at 11:14 AM, Andrea Suisani
>>> wrote:
sorry to come late to the party, but being in a similar conditio
Sent this to Claudio rather than the whole list ... here it is.
On Wed, Oct 10, 2012 at 7:44 AM, Claudio Freire wrote:
> On Wed, Oct 10, 2012 at 9:52 AM, Shaun Thomas
> wrote:
> > On 10/09/2012 06:30 PM, Craig James wrote:
> >
> >>ra:8192 walb:1M ra
distance prevents me from going to the co-location facility
and trying this with hyperthreading turned back on.
Craig
On Tue, Oct 9, 2012 at 1:12 PM, Craig James wrote:
> I've confirmed that hyperthreading causes a huge drop in performance on a
> 2x4-core Intel Xeon E5620 2.40
I've confirmed that hyperthreading causes a huge drop in performance on a
2x4-core Intel Xeon E5620 2.40GHz system. The bottom line is:
~3200 TPS max with hyperthreading
~9000 TPS max without hyprethreading
Here are my results.
"Hyprethreads" (Run1) is "out of the box" with hyperthreads ena
On Tue, Oct 9, 2012 at 9:02 AM, Shaun Thomas wrote:
> On 10/08/2012 06:40 PM, Craig James wrote:
>
> Nobody has commented on the hyperthreading question yet ... does it
>> really matter? The old (fast) server has hyperthreading disabled, and
>> the new (slower) server has
On Tue, Oct 9, 2012 at 9:14 AM, David Thomas wrote:
> On Mon, Oct 08, 2012 at 04:40:31PM -0700, Craig James wrote:
> >Nobody has commented on the hyperthreading question yet ... does it
> >really matter? The old (fast) server has hyperthreading disabled, and
> &g
On Mon, Oct 8, 2012 at 3:29 PM, Craig James wrote:
> One mistake in my descriptions...
>
> On Mon, Oct 8, 2012 at 2:45 PM, Craig James wrote:
>
>> This is driving me crazy. A new server, virtually identical to an old
>> one, has 50% of the performance with pgbench. I
On Mon, Oct 8, 2012 at 3:50 PM, Claudio Freire wrote:
> On Mon, Oct 8, 2012 at 7:48 PM, Craig James wrote:
> >> > # blockdev --getra /dev/sdb1
> >> > 256
> >>
> >>
> >> It's probably this. 256 is way too low to saturate your I/O system.
On Mon, Oct 8, 2012 at 3:44 PM, Claudio Freire wrote:
> On Mon, Oct 8, 2012 at 7:25 PM, Craig James wrote:
> >> > But why? What have I overlooked?
> >>
> >> Do you have readahead properly set up on the new one?
> >
> >
> > # blockdev --getra
On Mon, Oct 8, 2012 at 3:33 PM, Evgeny Shishkin wrote:
>
> On Oct 9, 2012, at 1:45 AM, Craig James wrote:
>
> One dramatic difference I noted via vmstat. On the old server, the I/O
> load during the bonnie++ run was steady, like this:
>
> procs ---memory---
One mistake in my descriptions...
On Mon, Oct 8, 2012 at 2:45 PM, Craig James wrote:
> This is driving me crazy. A new server, virtually identical to an old
> one, has 50% of the performance with pgbench. I've checked everything I
> can think of.
>
> The setups (call t
On Mon, Oct 8, 2012 at 3:09 PM, Claudio Freire wrote:
> On Mon, Oct 8, 2012 at 7:06 PM, Craig James wrote:
> >> Sequential Input on the new one is 279MB/s, on the old 400MB/s.
> >>
> >
> > But why? What have I overlooked?
>
> Do you have readahead properl
On Mon, Oct 8, 2012 at 2:57 PM, Evgeny Shishkin wrote:
>
> On Oct 9, 2012, at 1:45 AM, Craig James wrote:
>
> I tested both the RAID10 data disk and the RAID1 xlog disk with bonnie++.
> The xlog disks were almost identical in performance. The RAID10 pg-data
> disks looked
This is driving me crazy. A new server, virtually identical to an old one,
has 50% of the performance with pgbench. I've checked everything I can
think of.
The setups (call the servers "old" and "new"):
old: 2 x 4-core Intel Xeon E5620
new: 4 x 4-core Intel Xeon E5606
both:
memory: 12 GB DD
On 9/27/2012 1:56 PM, M. D. wrote:
>>
>> I'm in Belize, so what I'm considering is from ebay, where it's unlikely
>> that I'll get the warranty. Should I consider some other brand rather? To
>> build my own or buy custom might be an option too, but I would not get any
>> warranty.
Your best warra
On Thu, Sep 27, 2012 at 12:11 PM, M. D. wrote:
> Hi everyone,
>
> I want to buy a new server, and am contemplating a Dell R710 or the newer
> R720. The R710 has the x5600 series CPU, while the R720 has the newer
> E5-2600 series CPU.
>
> At this point I'm dealing with a fairly small database of 8
On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky wrote:
> Hi
> I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
> the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
> 990X).
>
Did you compile the AMD code on the AMD system?
We use a different open-sourc
On Tue, Aug 21, 2012 at 1:03 PM, Scott Marlowe wrote:
> That seems unnecessarily complex. how about this:
>
> create sequence s;
> select array_agg (a.b) from (select nextval('s') as b from
> generate_series(1,1000)) as a;
>
> Then you just iterate that array for the ids you need.
For brevity I
On Mon, Aug 20, 2012 at 5:10 PM, Tom Lane wrote:
> Craig James writes:
>> I want to do this:
>
>> select setval('object_id_seq', nextval('object_id_seq') + 1000, false);
>
>> Now suppose two processes do this simultaneously. Maybe they'
On Mon, Aug 20, 2012 at 6:06 PM, Scott Marlowe wrote:
> On Mon, Aug 20, 2012 at 6:59 PM, Scott Marlowe
> wrote:
>> On Mon, Aug 20, 2012 at 6:10 PM, Tom Lane wrote:
>>> Craig James writes:
>>>> I want to do this:
>>>
>>>> select s
Is seq.setval() "non transactional" in the same sense as seq.nextval()
is? More specifically, suppose I sometimes want to get IDs one-by-one
using nextval(), but sometimes I want a block of a thousand IDs. To
get the latter, I want to do this:
select setval('object_id_seq', nextval('object_i
On Tue, Aug 7, 2012 at 3:22 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
>> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>>>>
>>>> IF current_user = 'b
On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>>> I found this discussion from 2005 that says you can drop and restore a
>>> trigger inside a transacti
I found this discussion from 2005 that says you can drop and restore a
trigger inside a transaction, but that doing so locks the whole table:
http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php
> From: Jeff Davis
>
> It got me curious enough that I tested it, and apparently droping a
"building of interest" property.
The inner query would reduce your sample set to a much smaller set of
buildings, and presumably the outer query could handle that pretty quickly.
Craig James
>
> My questions:
>
> 1. Any comments about the nature of this problem?
>
On Tue, Jul 31, 2012 at 1:50 AM, Laszlo Nagy wrote:
>
> When Intel RAID controller is that? All of the ones on the motherboard
>> are pretty much useless if that's what you have. Those are slower than
>> software RAID and it's going to add driver issues you could otherwise
>> avoid. Better to
On Tue, Jul 24, 2012 at 11:27 AM, Laszlo Nagy wrote:
>
> > I wonder if UFS has better performance or not. Or can you suggest
>> > another fs? Just of the PGDATA directory.
>>
>
> Relying on physically moving a disk isn't a good backup/recovery
> strategy. Disks are the least reliable single co
>
> On 24/07/2012 14:51, Laszlo Nagy wrote:
> >
> > Hello,
> >
> > Under FreeBSD 9, what filesystem should I use for PostgreSQL? (Dell
> > PowerEdge 2900, 24G mem, 10x2T SATA2 disk, Intel RAID controller.)
> >
> > * ZFS is journaled, and it is more independent of the hardware. So if
> >the c
On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos wrote:
> Hello,
> The Postres 9.0 database we use gets about 20K inserts per minute. As long
> as you don't query at the same time the database is copying fine. However
> long running queries seems to delay so much the db that the applicati
On Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton wrote:
> On 11/07/12 21:18, Craig James wrote:
>
>>
>> It strikes me as a contrived case rather than a use case. What sort of
>> app repeatedly fills and truncates a small table thousands of times ...
>> other than a t
On Wed, Jul 11, 2012 at 7:05 AM, Tom Lane wrote:
> Daniel Farina writes:
> > TRUNCATE should simply be very nearly the fastest way to remove data
> > from a table while retaining its type information, and if that means
> > doing DELETE without triggers when the table is small, then it should.
>
wraparound if you have a fairly busy database. I can't
> think of a single situation where either client caching or LIMIT/OFFSET
> can't supplant it with better risk levels and costs.
>
A good solution to this general problem is "hitlists." I wrote about this
con
Any thoughts about this? It seems to be a new database system designed
from scratch to take advantage of the growth in RAM size (data sets that
fit in memory) and the availability of SSD drives. It claims to be "the
world's fastest database."
http://www.i-programmer.info/news/84-database/4397-me
t to ensure we always use prepared statements and variable
> bindings to help protect from SQL injection vulnerabilities.
>
JDBC has some features that are supposed to be convenient (automatic
preparing based on a number-of-executions threshold) that strike me as
misguided. It's one thin
On Thu, May 31, 2012 at 4:01 PM, Trevor Campbell wrote:
> On 01/06/12 08:55, Craig James wrote:
>
>
>
> On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell
> wrote:
>
>> We are having trouble with a particular query being slow in a strange
>> manner.
>>
On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell wrote:
> We are having trouble with a particular query being slow in a strange
> manner.
>
> The query is a join over two large tables that are suitably indexed.
>
> select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE,
> CI.FIELD,
On Fri, May 25, 2012 at 4:58 AM, Greg Spiegelberg wrote:
> On Sun, May 13, 2012 at 10:01 AM, Craig James wrote:
>
>>
>> On Sun, May 13, 2012 at 1:12 AM, Віталій Тимчишин wrote:
>>
>>>
>>> The sequences AFAIK are accounted as relations. Large list of r
1 - 100 of 570 matches
Mail list logo
