Greg, sorry for the resent: I had forgotten to include the list.
On Wed, Oct 5, 2016 at 2:34 PM, Greg Spiegelberg wrote:
> Data is not static. The 4M tables fall into one of two groups.
>
> Group A contains 2M tables. INSERT will occur ~100 times/day and maximum
> number of records anticipated
On Fri, Jul 22, 2016 at 12:14 AM, Jim Nasby wrote:
> On 7/21/16 4:59 PM, Tom Lane wrote:
>>>
>>> > As for function plans, ISTM that could be added to the PL handlers if
>>> > we
>>> > wanted to (allow a function invocation to return an array of explain
>>> > outputs).
>>
>> Where would you put tho
Hi,
I was wondering whether there are any plans to include the plan of the
FK check in EXPLAIN output. Or is there a different way to get to see
all the plans of triggers as well as of the main SQL?
When researching I found this thread from 2011 and the output format
does not seem to have changed
On Sat, May 14, 2016 at 1:11 AM, Gerardo Herzig wrote:
> Oh, so *all* the transactions are being slowed down at that point...What
> about CPU IO Wait% at that moment? Could be some other processes stressing
> the system out?
Or the database has just grown pass the size where disk caching is
effi
On Mon, May 9, 2016 at 11:41 PM, SoDupuDupu wrote:
> Владимир-3 wrote
>> It seems my quite complex query runs 10 times faster on "some_column
>> LIKE '%test_1' " vs "some_column LIKE 'test_1' "
>> So I just add "%" to the pattern...
>
> Keep in mind then LIKE '%test_1' and LIKE 'test_1' are not eq
On 01.05.2015 13:06, David Osborne wrote:
Simple... that did it... thanks!
dev=> create index on table(code,row_id);
CREATE INDEX
Time: 38088.482 ms
dev=> explain (analyse,buffers) select row_id as last_row_id from table
where code='XX' order by row_id desc limit 1;
Just out of curiosity: Is
On Mon, Nov 24, 2014 at 12:02 PM, Vlad Arkhipov wrote:
> Hello,
>
> I wonder why Postgres does not use index in the query below? It is a quite
> common use-case when you want to sort records by an arbitrary set of
> columns but do not want to create a lot of compound indexes for all possible
> com
On Sat, Nov 8, 2014 at 2:11 PM, Ruben Domingo Gaspar Aparicio wrote:
> The slave (I don't have control on the master) is using 2 NFS file systems,
> one for WALs and another one for the data, on Netapp controllers:
>
> dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs
> (rw,remount,
On 22.05.2013 16:37, Niels Kristian Schjødt wrote:
In reality the adverts that are selected is all 'active'. I'm hence
wondering if it theoretically (and in reality of cause) would make my
query faster if I did something like: "SELECT .* FROM cars LEFT
OUTER JOIN adverts on cars.id = adverts.ca
Hi Henk,
On Sun, Oct 14, 2012 at 9:04 AM, henk de wit wrote:
> Hi,
>
> For some reason the mailinglist software seems to block the email as soon as
> the planner details are in it, so I pasted those on pastebin.com:
> http://pastebin.com/T5JTwh5T
Just an additional data point: for whatever reaso
On Fri, Jul 20, 2012 at 4:27 AM, mark wrote:
> We have put some deferred constraints (some initially immediate, some
> initially deferred) into our database for testing with our applications.
> I understand a lot more may have to be tracked through a transaction and
> there could be some impact f
On Thu, Aug 2, 2012 at 4:54 PM, Russell Keane wrote:
> ** **
>
> Using PG 9.0 and given 2 queries (messageq_current is a view on the
> messageq_table):
>
> ** **
>
> select entity_id from messageq_current
>
> where entity_id = 123456;
>
> ** **
>
> select entity_id from messageq_curre
On Sat, Jul 14, 2012 at 11:50 AM, B Sreejith wrote:
> Dear All,
> Thanks alot for all the invaluable comments.
Additionally to Craig's excellent advice to measurements there's
something else you can do: with the knowledge of the queries your
application fires against the database you can evaluate
On Tue, Jul 10, 2012 at 10:21 AM, Sreejith Balakrishnan
wrote:
> Is there any tool or some sort of script available, for PostgreSQL, which
> can be used to measure scalability of an application's database. Or is there
> any guideline on how to do this.
>
> I am a bit confused about the concept of
On Wed, Jul 4, 2012 at 1:13 AM, Reza Taheri wrote:
> Checking online, the subject of clustered indexes for PostgreSQL comes up
> often. PGSQL does have a concept called “clustered table”, which means a
> table has been organized in the order of an index. This would help with
> sequential accesses
On Sat, Jun 9, 2012 at 7:58 PM, Kevin Kempter
wrote:
> Hi All;
>
> We have a client that has a table where large blobs (bytea) are stored. the
> table has a key column that is numbers (like 112362) but unfortunately it's
> a varchar column so the blobs are accessed via queries like:
>
> select * f
On Thu, Jun 7, 2012 at 9:53 AM, Nir Zilberman wrote:
> We are handling multiple concurrent clients connecting to our system -
> trying to get a license seat (each license has an initial capacity of
> seats).
> We have a table which keeps count of the acquired seats for each license.
> When a clien
On Thu, May 31, 2012 at 4:07 PM, Tatsuo Ishii wrote:
>> On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii wrote:
>>> Just for record, I rerun the test again with my single-LOCK patch, and
>>> now total runtime of pg_dump is 113 minutes.
>>> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with
On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii wrote:
> Just for record, I rerun the test again with my single-LOCK patch, and
> now total runtime of pg_dump is 113 minutes.
> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch).
>
> So far, I'm glad to see 40% time savings at thi
On Tue, May 15, 2012 at 7:53 PM, Greg Sabino Mullane wrote:
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
Is it established practice in the Postgres world to separate indexes
from tables? I would assume that the reasoning of Richard Foote -
albeit for Oracle databases
Hi,
On Tue, May 15, 2012 at 12:57 PM, Andres Freund wrote:
> I would rather suggest going with a suming table if you need to do something
> like that:
>
> sequence_id | value
> 1 | 3434334
> 1 | 1
> 1 | -1
> 1 | 1
> 1 | 1
> ...
>
> You then can get the current value with SELECT SUM(value) WHERE
On Sun, May 13, 2012 at 10:12 AM, Віталій Тимчишин wrote:
> 2012/5/11 Robert Klemme
>> On the contrary: what would be the /advantage/ of being able to create
>> millions of sequences? What's the use case?
>
> We are using sequences as statistics counters - they produ
On Fri, May 11, 2012 at 12:50 PM, Vidhya Bondre wrote:
> Is there any max limit set on sequences that can be created on the database
> ? Also would like to know if we create millions of sequences in a single db
> what is the downside of it.
On the contrary: what would be the /advantage/ of being
On Wed, May 9, 2012 at 5:45 PM, Claudio Freire wrote:
> On Wed, May 9, 2012 at 12:41 PM, Robert Klemme
> wrote:
>> I am not sure whether the replicant can be triggered to commit to disk
>> before the commit to disk on the master has succeeded; if that was the
>>
On Wed, May 9, 2012 at 3:58 PM, Merlin Moncure wrote:
> On Wed, May 9, 2012 at 8:06 AM, MauMau wrote:
>> I've heard from some people that synchronous streaming replication has
>> severe performance impact on the primary. They said that the transaction
>> throughput of TPC-C like benchmark (perhap
On Fri, May 4, 2012 at 3:04 PM, Merlin Moncure wrote:
> let's see the query plan...when you turned it off, did it go faster?
> put your suspicious plans here: http://explain.depesz.com/
I suggest to post three plans:
1. insert into temp table
2. access to temp table before analyze
3. access to
On Mon, May 7, 2012 at 4:25 PM, Thomas Kellerer wrote:
> That seems to be a documentation bug.
> I tried it, and it definitely does not work (or I am missing something).
Apparently I am the one who is missing something. :-)
> Their release notes at:
> http://www.oracle.com/technetwork/developer-
On Mon, May 7, 2012 at 2:11 PM, Thomas Kellerer wrote:
> Robert Klemme, 07.05.2012 14:03:
>>
>> Alternative tools for JDBC tests:
>>
>> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
>
> SQL Developer does not support Postgre
On Thu, May 3, 2012 at 5:40 PM, Merlin Moncure wrote:
> On Thu, May 3, 2012 at 10:28 AM, Ronald Hahn, DOCFOCUS INC.
> wrote:
>> After some testing using wiershark (poor mans profiler) to see what was
>> going on with the network I found that it was the tools I've been using.
>> Both Aqua and PGad
Hi Jan,
On Thu, May 3, 2012 at 4:10 AM, Jan Nielsen wrote:
> Below is the hardware, firmware, OS, and PG configuration pieces that I'm
> settling in on. As was noted, the local storage used for OS is actually two
> disks with RAID 10. If anything appears like a mistake or something is
> missing,
On Wed, Apr 25, 2012 at 7:08 PM, Greg Sabino Mullane wrote:
>> Is it established practice in the Postgres world to separate indexes
>> from tables? I would assume that the reasoning of Richard Foote -
>> albeit for Oracle databases - is also true for Postgres:
>
> Yes, it's an established practi
On Tue, Apr 24, 2012 at 4:56 AM, Jan Nielsen
wrote:
> We are considering the following drive allocations:
>
> * 4 x 15k SAS drives, XFS, RAID 10 on SAN for PG data
> * 4 x 15k SAS drives, XFS, RAID 10 on SAN for PG indexes
> * 2 x 15k SAS drives, XFS, RAID 1 on SAN for PG xlog
> * 1 x 15k SA
On Fri, Oct 14, 2011 at 8:19 PM, Josh Berkus wrote:
> Vishnu,
>
>> I am using PostgreSQL 8.4 in windows. I have created a database and
>> some tables on it. Also created a table space and some tables in it. My
>> application inserts data into these tables in every second. The
>> application is a
On Tue, Oct 4, 2011 at 4:06 PM, Anssi Kääriäinen
wrote:
> On 10/04/2011 04:27 PM, Robert Klemme wrote:
>>
>> On Tue, Oct 4, 2011 at 11:39 AM, Anssi Kääriäinen
>> wrote:
>>>
>>> I have the following setup:
>>>
>>> create table t
On Tue, Oct 4, 2011 at 11:39 AM, Anssi Kääriäinen
wrote:
> I have the following setup:
>
> create table test(id integer, seq integer);
> insert into test select generate_series(0, 100), generate_series(0, 1000);
> create unique index test_idx on test(id, seq);
> analyze test;
>
> Now I try to fetc
On Tue, Sep 20, 2011 at 7:43 PM, Thomas Kappler wrote:
> [please CC, I'm not on the list]
>
> Hi all,
>
> we have one table that basically uses Postgres as a key-value store.
>
> Table "public.termindex"
> Column | Type | Modifiers
> -+-+---
> subject_id | int
On Mon, Sep 19, 2011 at 4:04 PM, Merlin Moncure wrote:
> On Sun, Sep 18, 2011 at 9:59 AM, Stefan Keller wrote:
>> Merlin and Jeff,
>>
>> General remark again:It's hard for me to imagine that btree is
>> superior for all the issues mentioned before. I still believe in hash
>> index for primary key
On Sun, Sep 18, 2011 at 9:31 PM, Stefan Keller wrote:
> I'm simply referring to literature (like the intro Ramakrishnan & Gehrke).
> I just know that Oracle an Mysql actually do have them too and use it
> without those current implementation specific restrictions in
> Postgres.
Where exactly do y
On 13.09.2011 20:57, Stefan Keller wrote:
Interesting debate.
Indeed.
2011/9/13 Marti Raudsepp:
Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
SAVEPOINT it can roll back to in case of an error.
Are you sure? In theory I always understood that there are no
"subtransacti
On 13.09.2011 20:11, Marti Raudsepp wrote:
On Tue, Sep 13, 2011 at 19:34, Robert Klemme wrote:
I don't think so. You only need to catch the error (see attachment).
Or does this create a sub transaction?
Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
SAVEPOINT i
On Tue, Sep 13, 2011 at 5:13 PM, Marti Raudsepp wrote:
> On Tue, Sep 13, 2011 at 00:26, Robert Klemme
> wrote:
>> In the case of PG this particular example will work:
>> 1. TX inserts new PK row
>> 2. TX tries to insert same PK row => blocks
>> 1. TX commits
On 12.09.2011 19:22, Andy Colson wrote:
On 9/12/2011 12:15 PM, Robert Klemme wrote:
On 11.09.2011 19:02, Marti Raudsepp wrote:
On Sun, Sep 11, 2011 at 17:23, Andy Colson wrote:
On 09/11/2011 08:59 AM, Igor Chudov wrote:
By the way, does that INSERT UPDATE functionality or something like
this
On 11.09.2011 19:02, Marti Raudsepp wrote:
On Sun, Sep 11, 2011 at 17:23, Andy Colson wrote:
On 09/11/2011 08:59 AM, Igor Chudov wrote:
By the way, does that INSERT UPDATE functionality or something like this exist
in Postgres?
You have two options:
1) write a function like:
create function
On 11.09.2011 22:10, Scott Marlowe wrote:
Another data point. We had a big Oracle installation at my last job,
and OLAP queries were killing it midday, so I built a simple
replication system to grab rows from the big iron Oracle SUN box and
shove into a single core P IV 2.xGHz machine with 4 12
On Sat, Aug 20, 2011 at 8:33 PM, Midge Brown wrote:
> Robert,
>
> I was largely looking for input on whether I may have inadvertently shot
> myself in the foot with some of the choices I made when setting up
> postgresql 9.0, which is on different hardware than was the 7.4 setup.
OK, I though the
On Thu, Aug 18, 2011 at 11:55 PM, Midge Brown wrote:
> I'm in the process of upgrading from postgres 7.4.8 to 9.0.4 and wanted to
> run my decisions past some folks who can give me some input on whether my
> decisions make sense or not.
I am not sure what decisions you actually refer to here: in
On Wed, Aug 3, 2011 at 6:24 PM, Merlin Moncure wrote:
> On Wed, Aug 3, 2011 at 2:18 AM, Robert Klemme
> wrote:
>>>> Another observation: That criterion looks suspicious to me. I would
>>>> expect any RDBMS to be better able to optimize this:
>>>>
>&g
On Wed, Aug 3, 2011 at 3:27 PM, Li Jin wrote:
> Robert，
> I've built an index on this expression firstname || ' ' ||
> substring(lastname,1,1). I believe this is the best index for this
> particular query. Correct me if I am wrong.
Maybe, maybe not. Difficult to tell from a distance. I would ha
On Wed, Aug 3, 2011 at 3:27 PM, Li Jin wrote:
> Robert，
> I've built an index on this expression firstname || ' ' ||
> substring(lastname,1,1). I believe this is the best index for this
> particular query. Correct me if I am wrong.
Maybe, maybe not. Difficult to tell from a distance. I would ha
On Tue, Aug 2, 2011 at 11:48 PM, Merlin Moncure wrote:
> On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme
> wrote:
>> On Thu, Jul 28, 2011 at 11:00 PM, Li Jin wrote:
>>> I met with the problem that when I was using WITH clause to reuse a
>>> subquery, I got a huge
On Sat, Jul 30, 2011 at 3:01 AM, Gavin Flower
wrote:
> On 24/07/11 03:58, alan wrote:
>>>
>>> My first approach would be to remove WeekAvg and MonthAvg from the
>>> table and create a view which calculates appropriate values.
>>
>> Thanks Robert, I had to upgrade to 9.0.4 to use the extended windo
On Thu, Jul 28, 2011 at 11:00 PM, Li Jin wrote:
> I met with the problem that when I was using WITH clause to reuse a
> subquery, I got a huge performance penalty because of query planner.
> Here are the details, the original query is
> EXPLAIN ANALYZE WITH latest_identities AS
> (
> SELECT DI
On Thu, Jul 14, 2011 at 4:05 PM, Kevin Grittner
wrote:
> Tom Lane wrote:
>
>> It seems like we ought to distinguish heap cleanup activities from
>> user-visible semantics (IOW, users shouldn't care if a HOT cleanup
>> has to be done over after restart, so if the transaction only
>> wrote such rec
On Tue, Jul 12, 2011 at 9:41 AM, alan wrote:
> Hello,
> I'm a postgres newbie and am wondering what's the best way to do this.
>
> I am gathering some data and will be inserting to a table once daily.
> The table is quite simple but I want the updates to be as efficient as
> possible since
> this
On Mon, Jul 11, 2011 at 3:13 PM, k...@rice.edu wrote:
> I do not know if this makes sense in PostgreSQL and that readers
> do not block writers and writes do not block readers. Are your
> UPDATEs to individual rows, each in a separate transaction, or
> do you UPDATE multiple rows in the same trans
On Wed, Jul 6, 2011 at 9:04 PM, Tomas Vondra wrote:
> Dne 6.7.2011 15:30, bakkiya napsal(a):
>> Any help, please?
>
> According to the EXPLAIN ANALYZE output (please, don't post it to the
> mailing list directly - use something like explain.depesz.com, I've done
> that for you this time: http://ex
On Mon, Jun 27, 2011 at 5:37 PM, wrote:
>> The mystery remains, for me: why updating 100,000 records could complete
>> in as quickly as 5 seconds, whereas an attempt to update a million
>> records was still running after 25 minutes before we killed it?
>
> Hi, there's a lot of possible causes. Us
On 06/22/2011 11:42 PM, Greg Smith wrote:
On 06/22/2011 05:27 PM, Marcus Engene wrote:
I have some tables with bitmask integers. Set bits are the interesting
ones. Usually they are sparse.
If it's sparse, create a partial index that just includes rows where the
bit is set:
http://www.postgresq
On 14.06.2011 18:29, Tom Lane wrote:
Svetlin Manavski writes:
I am really surprised to see that the planner needs me to explicitly specify
the same condition twice like this:
SD.detectorid = SS.detectorid
and SD.sessionid = SS.id
and SD.detectorid = 1
and SD.sessionid>= 1
On Thu, Jun 9, 2011 at 7:44 PM, Greg Smith wrote:
> **
> On 06/09/2011 07:43 AM, Willy-Bas Loos wrote:
>
> Well, after reading your article i have been reading some materail about it
> on the internet, stating that separating indexes from data for performance
> benefits is a myth.
> I found your
On Thu, Feb 10, 2011 at 7:32 PM, Craig James wrote:
> On 2/10/11 9:21 AM, Kevin Grittner wrote:
>>
>> Shaun Thomas wrote:
>>
>>> how difficult would it be to add that syntax to the JOIN
>>> statement, for example?
>>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>>
>> W
On Mon, May 23, 2011 at 5:54 PM, Dave Johansen wrote:
> I apologize for the multiple posts. I sent this email right after joining
> the list and after it hadn't shown up a day later I figured that it had been
> lost or something and sent the other one.
Sorry for the nitpicking but I even see _thr
Dave,
how often do you want to repeat that posting? What about instead
replying to the answers you got so far?
Cheers
robert
On Tue, May 17, 2011 at 5:31 PM, Dave Johansen wrote:
> I am using Postgres 8.3 and I have an issue very closely related to the one
> described here:
> http://archives
On Wed, May 18, 2011 at 5:26 PM, Dave Johansen wrote:
> I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
> tables but when I do a select on the view using a LIMIT, it scans the entire
> tables and takes significantly longer than writing out the query with the
> LIMITs in the
On Tue, May 17, 2011 at 11:47 AM, Craig Ringer
wrote:
> On 05/17/2011 03:00 PM, Robert Klemme wrote:
>
>> The main point is that you do not benefit from the larger IO bandwidth
>> if access patterns do not permit parallel access to both disks (e.g.
>> because you first n
On Mon, May 16, 2011 at 4:31 PM, Robert Haas wrote:
> On Mon, May 16, 2011 at 4:19 AM, Robert Klemme
> wrote:
>>> - If the planner chooses a Bitmap Index Scan, it effectively scans the
>>> index to figure out which table blocks to read, and then reads those
>>> t
On Fri, May 13, 2011 at 9:04 PM, Robert Haas wrote:
> On Wed, May 4, 2011 at 6:31 AM, Willy-Bas Loos wrote:
>> I'm asking them for (real) benchmarks, thanks for the advice. (fio is not
>> available for us now to do it myself, grmbl)
>> It just occurred to me that it is not necessarily the case th
On Thu, Apr 28, 2011 at 11:20 AM, Rishabh Kumar Jain wrote:
> How the tables must be ordered in the list of tables in from statement?
>
To achieve what? Generally there is no requirement for a particular
ordering of relation names in SQL.
Cheers
robert
--
remember.guy do |as, often| as.you_
On Tue, Apr 19, 2011 at 10:47 AM, Uwe Bartels wrote:
> Oh, I do care about these columns.
> But by using an group by on the key columns, I cannot select the columns as
> they are. Otherwise you get an error message.
> So I have to use an aggregate functionlike min().
I find that slightly contradi
On Tue, Apr 19, 2011 at 12:08 AM, Stefan Keller wrote:
> I browsed the faq and looked at PostgreSQL performance books but I
> could not find the obvious:
> How to configure a read-only database server?
>
> I have a single-disk virtual Linux system and a read-only dataset
> which is exposed to inte
On Mon, Apr 18, 2011 at 7:13 PM, Uwe Bartels wrote:
> the aggregate function I was talking about is the function I need to use for
> the non-group by columns like min() in my example.
> There are of course several function to choose from, and I wanted to know
> which causes as less as possible res
On Thu, Nov 25, 2010 at 4:46 PM, wrote:
>> I am not facing any issues, but yes I want to have optimal performance for
>> SELECT and INSERT, especially when I am doing these ops repeatedly.
>> Actually I am porting from Oracle to PG. Oracle starts a lot of processes
>> when
>> it needs to run many
On Fri, Nov 19, 2010 at 10:50 AM, Louis-David Mitterrand
wrote:
> On Tue, Nov 16, 2010 at 05:28:19PM +0100, Harald Fuchs wrote:
>> In article <4ce2688b.2050...@tweakers.net>,
>> Arjen van der Meijden writes:
>>
>> > On 16-11-2010 11:50, Louis-David Mitterrand wrote:
>> >> I have to collect lots o
On 11/03/2010 04:52 PM, Nick Matheson wrote:
We have an application that needs to do bulk reads of ENTIRE
Postgres tables very quickly (i.e. select * from table). We have
observed that such sequential scans run two orders of magnitude slower
than observed raw disk reads (5 MB/s versus 100 MB/s).
74 matches
Mail list logo
