On Mon, Oct 30, 2017 at 5:51 PM, patibandlakoshal
wrote:
> From performance standpoint I thought set operation was better than Cursor.
> But I found Cursor to be more effective than Set operation. Is there a way
> we can force optimizer to use cursor plan. QUERY PLAN
You're going to have to be
On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau
wrote:
> I’m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic).
> Hardware is:
>
> *2x Intel Xeon E5550
>
> *72GB RAM
>
> *Hardware RAID10 (4 x 146GB SAS 10k) P410i controller with 1GB FBWC (80%
> read/20% write) for Postgresql data
On Fri, Jun 23, 2017 at 6:33 PM, Tom Lane wrote:
> Clint Miller writes:
>> That's a good plan because it's not doing a quick sort. Instead, it's just
>> reading the sort order off of the index, which is exactly what I want. (I
>> had to disable enable_sort because I didn't have enough rows of tes
On Thu, Jun 8, 2017 at 11:05 AM, Jeremy Finzel wrote:
> On Thu, Jun 8, 2017 at 9:58 AM, Tom Lane wrote:
>> Merlin Moncure writes:
>> > Having said that, what I typically do in such
>> > cases (this comes a lot in database driven work queues) something like
>> &
On Mon, May 22, 2017 at 10:17 AM, Ariel wrote:
>
> I need to be able to quickly find rows where a column is not null (only a
> small percent of the rows will have that column not null).
>
> Should I do:
>
> CREATE INDEX ON table ((col IS NOT NULL)) WHERE col IS NOT NULL
>
> or:
>
> CREATE INDEX ON
On Sun, May 21, 2017 at 5:37 PM, Tom Lane wrote:
> Riaan Stander writes:
>> The intended use is use-once. The reason is that the statements might
>> differ per call, especially when we start doing updates. The ideal would
>> be to just issue the sql statements, but I was trying to cut down on
>>
On Mon, May 8, 2017 at 3:23 PM, Hans Braxmeier
wrote:
> Hello,
>
>
> on our old server (120 GB RAM) PostgreSQL 9.4.5 was using less than 10 GB of
> ram. On our new server (same system) Postgres 9.4.11 is using up to 40 GB
> Ram. Especially each idle process is consuming 2.4 GB: postgres 30764 8.
On Fri, Mar 24, 2017 at 2:47 PM, Scott Marlowe wrote:
> On Fri, Mar 24, 2017 at 3:58 AM, Pietro Pugni wrote:
>> Hi there,
>> I’m running PostgreSQL 9.6.2 on Ubuntu 16.04.2 TLS (kernel
>> 4.4.0-66-generic). Hardware is:
>> - 2 x Intel Xeon E5-2690
>> - 96GB RAM
>> - Software mdadm RAID10 (6 x S
On Mon, Mar 6, 2017 at 7:20 AM, Piotr Gasidło wrote:
> We are having some performance issues after we upgraded to newest
> version of PostgreSQL, before it everything was fast and smooth.
>
> Upgrade was done by pg_upgrade from 9.4 directly do 9.6.1. Now we
> upgraded to 9.6.2 with no improvement.
t; 16 to get higher iops) and the benchmarks returned expected results. I guess
> how this relates with Postgres.. probably effective_io_concurrency, as
> suggested by Merlin Moncure, should be the counterpart of numjob in fio?
Kind of. effective_io_concurrency allows the database to send >
On Tue, Feb 21, 2017 at 1:40 PM, Wes Vaske (wvaske)
wrote:
> - HW RAID can give better performance if your drives do not have
> a capacitor backed cache (like the MX300) AND the controller has a battery
> backed cache. **Consumer drives can often get better performance from HW
> RAID**.
On Tue, Feb 21, 2017 at 7:49 AM, Pietro Pugni
wrote:
> Hi there,
> I configured an IBM X3650 M4 for development and testing purposes. It’s
> composed by:
> - 2 x Intel Xeon E5-2690 @ 2.90Ghz (2 x 8 physical Cores + HT)
> - 96GB RAM DDR3 1333MHz (12 x 8GB)
> - 2 x 146GB SAS HDDs @ 15k rpm confi
On Thu, Feb 2, 2017 at 1:18 AM, Jeff Janes wrote:
> On Wed, Feb 1, 2017 at 4:38 AM, Merlin Moncure wrote:
>>
>> I was just troubleshooting a strange performance issue with pg_trgm
>> (greatest extension over) that ran great in testing but poor in
>> production follo
I was just troubleshooting a strange performance issue with pg_trgm
(greatest extension over) that ran great in testing but poor in
production following a 9.6 in place upgrade from 9.2. By poor I mean
7x slower. Problem was resolved by ALTER EXTENSION UPDATE followed by
a REINDEX on the impacted t
On Mon, Jan 23, 2017 at 9:10 AM, Jim Nasby wrote:
> On 1/18/17 6:09 PM, David G. Johnston wrote:
>>
>> That would not be a productive exercise for me, or most people who just
>> want
>> some idea of what to expect in terms of behavior when they write and use a
>> Stable function (Immutable and Vol
On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique wrote:
> @Merlin Moncure
>>
>> Big gains (if any) are likely due to indexing strategy.
>> I do see some suspicious casting, for example:
>> Join Filter: ((four_charlie.delta_tango)::integer =
>> (six_quebec.golf_br
On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique wrote:
> Hi there, fellow experts!
>
> I need an advice with query that became slower after 9.3 to 9.6 migration.
>
> First of all, I'm from the dev team.
>
> Before migration, we (programmers) made some modifications on query bring
> it's average t
On Wed, Nov 30, 2016 at 6:26 AM, Marc-Olaf Jaschke
wrote:
> Hi,
>
> i have a performance issue with bitmap index scans on huge amounts of big
> jsonb documents.
>
>
> = Background =
>
> - table with big jsonb documents
> - gin index on these documents
> - queries using index conditions wi
On Mon, Nov 14, 2016 at 11:36 AM, domenico febbo
wrote:
> dear Pietro,
> are you sure about
>
> effective_io_concurrency = 30
>
> could you please explain the type of disk storage?
fast storage can certainly utilize high settings of
effective_io_concurrency at least in some cases...for example se
On Wed, Aug 17, 2016 at 6:45 AM, pinker wrote:
> Hi,
> the problem I'm dealing with is long holding locks during extensions of
> table:
> process xxx still waiting for ExclusiveLock on extension of relation xxx of
> database xxx after 3000.158 ms
> My application is write intensive, in one round I
On Tue, Aug 9, 2016 at 6:27 PM, Suya Huang wrote:
> Hi,
> I’ve got a SQL runs for about 4 seconds first time it’s been executed,but
> very fast (20ms) for the consequent runs. I thought it’s because that the
> first time table being loaded into memory. However, if you change the where
> clause val
On Wed, Jul 6, 2016 at 4:48 PM, Scott Marlowe wrote:
> On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure wrote:
>> Disabling write back cache for write heavy database loads will will
>> destroy it in short order due to write amplication and will generally
>> cause it to underp
On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote:
> Hello,
>
> I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book
> and I have some questions regarding the guidelines I found in the book,
> because I suspect some of them can't be followed blindly to the letter on a
> server w
On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev wrote:
> I tried to DELETE about 7 million rows at once, and the query went up to 15%
> of the RAM (120 GB in total), which pushed some indexes out and the server
> load went up to 250, so I had to kill the query.
>
> The involved table does not have
On Thu, Jun 9, 2016 at 8:43 AM, Nicolas Paris wrote:
>
>
> 2016-06-09 15:31 GMT+02:00 Merlin Moncure :
>>
>> On Wed, Jun 8, 2016 at 1:04 AM, Tom Lane wrote:
>> > Michael Paquier writes:
>> >> On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus wrote:
>>
On Wed, Jun 8, 2016 at 1:04 AM, Tom Lane wrote:
> Michael Paquier writes:
>> On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus wrote:
>>> On 06/07/2016 08:42 AM, Nicolas Paris wrote:
Will this 1GO restriction is supposed to increase in a near future ?
>
>>> Not planned, no. Thing is, that's the
On Tue, Apr 26, 2016 at 10:27 AM, Artem Tomyuk wrote:
> I didn't compare impact of virtualization on other hypervisors yet.
My rule of thumb is 50% hit for 1:1 host:guest. Virtualization is
not free. If that's a pain try using 100% native solutions (docker,
etc)
merlin
--
Sent via pgsql-pe
On Sun, Apr 24, 2016 at 3:14 PM, bricklen wrote:
> Query plan for the md5() index test:
>
> Index Scan using lots_of_columns_md5_idx on lots_of_columns
> (cost=0.93..3.94 rows=1 width=208) (actual time=0.043..0.043 rows=1 loops=1)
>Index Cond: ('1ba23a0668ec17e230d98c270d6664dc'::text =
> md5
On Thu, Apr 21, 2016 at 4:49 AM, Sven Kerkling wrote:
> Can somebody help me with these performance Problem.
>
> What can I try to solve this?
can you explain what the problem actually is? Which query is running
slow and how fast do you think it should run?
merlin
--
Sent via pgsql-performan
On Mon, Feb 29, 2016 at 12:47 PM, Tom Lane wrote:
> FWIW, PG >= 9.5 will ignore a LIMIT 1 inside an EXISTS, so that you get
> the same plan with or without it. But that does act as an optimization
> fence in earlier releases.
Does 'offset 0' still work as it did?
merlin
--
Sent via pgsql-per
On Mon, Feb 8, 2016 at 8:35 AM, Merlin Moncure wrote:
> On Sat, Feb 6, 2016 at 6:03 AM, Filip Rembiałkowski
> wrote:
>> Thanks for the feedback.
>>
>> This patch is my first and obvious approach.
>>
>> @Merlin, I'm not sure if I get your idea:
>>
On Sat, Feb 6, 2016 at 6:03 AM, Filip Rembiałkowski
wrote:
> Thanks for the feedback.
>
> This patch is my first and obvious approach.
>
> @Merlin, I'm not sure if I get your idea:
> - keep previous behaviour as obligatory? (which is: automatic
> de-duplicating of incoming messages by channel+payl
On Fri, Feb 5, 2016 at 9:33 AM, Filip Rembiałkowski
wrote:
> patch submitted on -hackers list.
> http://www.postgresql.org/message-id/CAP_rwwn2z0gPOn8GuQ3qDVS5+HgEcG2EzEOyiJtcA=vpdeh...@mail.gmail.com
>
> results after the patch:
>
> trigger= BEGIN RETURN NULL; END
> rows=4
> 228ms COPY
On Sat, Nov 14, 2015 at 12:58 AM, Jamie Koceniak
wrote:
> Had the issue again today.
>
> Here is vmstat :
> procs ---memory-- ---swap-- -io -system-- cpu
> r b swpd free buff cache si sobibo in cs us sy id wa
> 24 0 0 1591718656 605656
On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane wrote:
> Merlin Moncure writes:
>> On Thu, Nov 12, 2015 at 9:48 AM, Craig James wrote:
>>> What about a warning on creation?
>>>
>>> db=> create table foo(i integer primary key);
>>> db=> create
On Thu, Nov 12, 2015 at 9:48 AM, Craig James wrote:
>
> On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure wrote:
>>
>> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane wrote:
>> > Massalin Yerzhan writes:
>> >> I'm having an issue. The query never ends:
>&
On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane wrote:
> Massalin Yerzhan writes:
>> I'm having an issue. The query never ends:
>> delete from bb_gamelist_league;
>
> 9 times out of 10, the answer to this type of problem is that you have
> some table referencing this one by a foreign key, and the refer
On Mon, Oct 26, 2015 at 12:45 PM, David Osborne wrote:
> Physical memory is 61GB at the moment.
>
> work_mem is 1,249,104kB
I'm not sure if this query is a candidate because of the function, but
you can try progressively cranking work_mem and running explain to see
what it'd take to get a hashagg
On Fri, Oct 23, 2015 at 12:45 PM, Jamie Koceniak
wrote:
> Hi,
>
> We just had the performance problem again today.
> Here is some of the top output. Unfortunately, we don't have perf top
> installed.
>
> top - 16:22:16 up 29 days, 13:00, 2 users, load average: 164.63, 158.62,
> 148.52
> Tasks:
On Friday, October 23, 2015, David Osborne wrote:
> Hi,
>
> Wondering if anyone could suggest how we could improve the performance of
> this type of query?
> The intensive part is the summing of integer arrays as far as I can see.
> We're thinking there's not much we can do to improve performance
On Wed, Oct 21, 2015 at 2:45 PM, Pavel Stehule wrote:
> 2015-10-21 21:32 GMT+02:00 Jamie Koceniak :
>>
>> adama_prod=# SHOW shared_buffers;
>>
>> shared_buffers
>>
>>
>>
>> 64GB
>
>
> can you try to increase shared buffers to 200GB and decrease effective cache
> size to 180GB? If
On Tue, Oct 20, 2015 at 12:34 PM, Jamie Koceniak
wrote:
> Version:
>
> ---
>
> PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
> 4.7.2-5) 4.7.2, 64-bit
>
> Query Plan
>
> http://expla
On Fri, Oct 16, 2015 at 9:14 PM, Jonathan Rogers
wrote:
> On 10/16/2015 08:37 AM, Albe Laurenz wrote:
>> Jonathan Rogers wrote:
Look at the EXPLAIN ANALYZE output for both the custom plan (one of the
first five executions) and the generic plan (the one used from the sixth
time on) a
On Wed, Oct 7, 2015 at 5:29 AM, FattahRozzaq wrote:
> Response from you all are very precious.
>
> @Merlin,
> I'm misunderstood the question.
> Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use
Can you be a little more specific. What values did you look at and
how did y
On Tue, Oct 6, 2015 at 10:10 AM, Scott Marlowe wrote:
> On Tue, Oct 6, 2015 at 3:33 AM, FattahRozzaq wrote:
>> @Merlin Moncure, I got the calculation using pg_tune. And I modified
>> the shared_buffers=24GB and the effective_cache_size=64GB
>>
>> @Igor Neyman,
>&g
On Mon, Oct 5, 2015 at 9:51 AM, FattahRozzaq wrote:
> I have configured postgresql.conf with parameters as below:
>
> log_destination = 'stderr'
> logging_collector = on
> log_directory = 'pg_log'
> listen_addresses = '*'
> log_destination = 'stderr'
> logging_collector = on
> log_directory = 'pg_
On Tue, Sep 8, 2015 at 4:21 PM, Tomas Vondra
wrote:
> On 09/08/2015 09:15 PM, Merlin Moncure wrote:
> ...
>>>
>>> I just had the exact same problem, and indeed gin fares much better.
>>
>>
>> Also, with 9.5 we will see much better worst case performanc
On Thu, Sep 3, 2015 at 6:19 PM, Claudio Freire wrote:
> On Wed, Sep 2, 2015 at 4:29 PM, Jeff Janes wrote:
>> On Wed, Sep 2, 2015 at 7:00 AM, Volker Böhm wrote:
>>>
>>>
>>>
>>> CREATE INDEX trgm_adresse ON adressen.adresse USING gist
>>> (normalize_string((btrimnormalize_stringCOALESC
On Mon, Jul 13, 2015 at 4:01 PM, Merlin Moncure wrote:
> On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt wrote:
>> Hi folks,
>>
>> I have a fairly simple three-table query (pasted below) with two LEFT JOINs
>> and an OR in the WHERE clause that for some reason is doing seque
On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt wrote:
> Hi folks,
>
> I have a fairly simple three-table query (pasted below) with two LEFT JOINs
> and an OR in the WHERE clause that for some reason is doing sequential scans
> on all three tables (two of them large -- several million rows), even though
On Thu, Jul 9, 2015 at 10:12 AM, Graeme B. Bell wrote:
>>>
>>> 3. I don't disagree that the benchmark code is objectively 'bad' in the
>>> sense that it is missing an important optimisation.
>>
>> Particularly with regards documentation, a patch improving things is
>> much more likely to improve
On Thu, Jul 9, 2015 at 4:44 AM, Graeme B. Bell wrote:
> On 09 Jul 2015, at 05:38, Tom Lane wrote:
>
>> If you
>> write your is_prime function purely in plpgsql, and don't bother to mark
>> it nonvolatile, *it will not scale*.
>
>> much for properly written plpgsql; but there's an awful lot of bad
On Wed, Jul 8, 2015 at 5:38 PM, Craig James wrote:
> 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
On Wed, Jul 8, 2015 at 12:48 PM, Craig James wrote:
> 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 int
On Tue, Jul 7, 2015 at 3:33 PM, Graeme B. Bell wrote:
>
> Hi Merlin,
>
> Long story short - thanks for the reply, but you're not measuring anything
> about the parallelism of code running in a pl/pgsql environment here. You're
> just measuring whether postgres can parallelise entering that envir
On Tue, Jul 7, 2015 at 11:46 AM, Graeme B. Bell wrote:
>>
>> RAID controllers are completely unnecessary for SSD as they currently
>> exist.
>
> Agreed. The best solution is not to buy cheap disks and not to buy RAID
> controllers now, imho.
>
> In my own situation, I had a tight budget, high per
> On 07 Jul 2015, at 16:59, Heikki Linnakangas wrote:
>
>>
>> So it lies about fsync()... The next question is, does it nevertheless
>> enforce the correct ordering of persisting fsync'd data? If you write to
>> file A and fsync it, then write to another file B and fsync it too, is it
>> guaran
On Thu, Jul 2, 2015 at 1:00 PM, Wes Vaske (wvaske)
wrote:
> Storage Review has a pretty good process and reviewed the M500DC when it
> released last year.
> http://www.storagereview.com/micron_m500dc_enterprise_ssd_review
>
>
>
> The only database-specific info we have available are for Cassandr
On Fri, Jul 3, 2015 at 9:48 AM, Graeme B. Bell wrote:
> Hi everyone,
>
> I've written a new open source tool for easily parallelising SQL scripts in
> postgres. [obligatory plug: https://github.com/gbb/par_psql ]
>
> Using it, I'm seeing a problem that I've also seen in other postgres proje
On Wed, Jul 1, 2015 at 6: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 controller cards
> available today.
>
> The database is about 750 GB. This is a "warehouse" server. We load supplie
On Thu, Jun 11, 2015 at 7:18 PM, Sasa Vilic wrote:
> Hi,
>
> I have a query that takes ridiculously long to complete (over 500ms) but if
> I disable nested loop it does it really fast (24.5ms)
>
> Here are links for
> * first request (everything enabled): http://explain.depesz.com/s/Q1M
> * second
On Wed, Jun 10, 2015 at 4:37 PM, Patrick Krecker wrote:
> On Wed, Jun 10, 2015 at 2:08 PM, Merlin Moncure wrote:
>> On Wed, Jun 10, 2015 at 3:55 PM, Patrick Krecker
>> wrote:
>>> OK. Well, fortunately for us, we have a lot of possible solutions this
>>> pro
On Wed, Jun 10, 2015 at 3:55 PM, Patrick Krecker wrote:
> OK. Well, fortunately for us, we have a lot of possible solutions this
> problem, and it sounds like actually getting statistics for attributes
> ? 'reference' is not realistic. I just wanted to make sure it wasn't
> some configuration erro
On Wed, Jun 10, 2015 at 2:40 PM, Josh Berkus wrote:
> On 06/10/2015 11:32 AM, Merlin Moncure wrote:
>> This is a fundamental issue with using 'database in a box' datatypes
>> like hstore and jsonb. They are opaque to the statistics gathering
>> system and s
On Wed, Jun 10, 2015 at 12:32 PM, Patrick Krecker wrote:
> Hi everyone --
>
> I had an issue the other day where a relatively simple query went from
> taking about 1 minute to execute to taking 19 hours. It seems that the
> planner chooses to use a materialize sometimes [1] and not other times
> [
On Mon, Jun 1, 2015 at 12:38 AM, Ashik S L wrote:
>> On 05/30/2015 09:46 AM, Ashik S L wrote:
>>> We are using postgres SQL version 8.4.17..
>>> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
>>> each row is of 60 bytes.Every time we insert 16380 bytes of data.
>>
>> Way bac
On Wed, Mar 4, 2015 at 1:31 PM, Soni M wrote:
> Hello All,
>
> Master db size 1.5 TB
> All postgres 9.1.13 installed from RHEL package.
> It has streaming replica and slony replica to another servers.
>
> Server performance is slower than usual, before that, there's a big query
> got cancelled and
On Tue, Jan 27, 2015 at 1:06 AM, Josh Berkus wrote:
> Folks,
>
> Currently, JSONB fields don't have statistics, and estimate a flat 1%
> selectivity. This can result in poor query plans, and I'm wondering if
> anyone has a suggested workaround for this short of hacking a new
> selectivity functio
On Mon, Jan 26, 2015 at 10:32 AM, Christian Roche
wrote:
> Bitmap Heap Scan on mixpanel_events_201409 (cost=7663.36..1102862.70
> rows=410022 width=949)
>
> Recheck Cond: (event_id = ANY
> ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]))
>
> -> Bitmap Index Scan on mixpanel_idx_event_20
On Fri, Jan 9, 2015 at 1:48 PM, Claudio Freire wrote:
> On Fri, Jan 9, 2015 at 4:26 PM, Steve Crawford
> wrote:
>> New hardware is quite different. 2x10-core E5-2660v3 @2.6GHz, 128GB
>> DDR4-2133 RAM and 800GB Intel DC P3700 NVMe PCIe SSD. In essence, the
>> dataset will fit in RAM and will be ba
On Wed, Dec 10, 2014 at 2:30 AM, Strahinja Kustudić
wrote:
> On Wed, Dec 10, 2014 at 4:55 AM, Mark Kirkwood
> wrote:
>>
>> That is interesting: I've done some testing on this type of card with 16
>> (slightly faster Hitachi) SSD attached. Setting WT and NORA should enable
>> the so-called 'fastpa
On Sat, Dec 6, 2014 at 7:08 AM, Bruce Momjian wrote:
> On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote:
>> effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
>> effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
>> effective_io
On Fri, Dec 5, 2014 at 12:46 AM, Simon Riggs wrote:
> On 30 September 2014 at 05:53, Simon Riggs wrote:
>> On 29 September 2014 16:00, Merlin Moncure wrote:
>>> On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs wrote:
>>>> The problem, as I see it, is different. We ass
On Wed, Nov 5, 2014 at 11:40 AM, Merlin Moncure wrote:
> I recently sourced a 300gb intel s3500 ssd to do some performance
> testing. I didn't see a lot of results on the web so I thought I'd
> post some numbers. Testing machine is my workstation crapbox with 4
> cores
I recently sourced a 300gb intel s3500 ssd to do some performance
testing. I didn't see a lot of results on the web so I thought I'd
post some numbers. Testing machine is my workstation crapbox with 4
cores and 8GB ram (of which about 4 is usable by the ~ 50gb database).
The drive cost 260$ at
On Tue, Oct 21, 2014 at 11:50 AM, Tom Lane wrote:
> Marco Di Cesare writes:
>> COUNT(DISTINCT "foxtrot_india"."bravo_romeo")
>
> Ah. That explains why the planner doesn't want to use a hash aggregation
> step --- DISTINCT aggregates aren't supported with those.
yup. With this q
On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare
wrote:
> We are using Postgres for the first time after being SQLServer users for a
> long time so forgive for being noobs.
>
>
>
> We are using a BI tool that generates a query with an unusually large number
> of joins. My understanding is that wit
On Tue, Sep 30, 2014 at 11:54 AM, Jeff Janes wrote:
> On Mon, Sep 29, 2014 at 7:12 PM, Gavin Flower
> wrote:
>>
>>
>> Would it be feasible to get a competent statistician to advise what data
>> to collect, and to analyze it? Maybe it is possible to get a better
>> estimate on how much of a table
On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs wrote:
> The problem, as I see it, is different. We assume that if there are
> 100 distinct values and you use LIMIT 1 that you would only need to
> scan 1% of rows. We assume that the data is arranged in the table in a
> very homogenous layout. When da
On Tue, Sep 23, 2014 at 7:58 AM, Mkrtchyan, Tigran
wrote:
> Hi Merlin,
>
> you are right, in 9.4 the debug_assertions are on:
>
> # /etc/init.d/postgresql-9.4 start
> Starting postgresql-9.4 service: [ OK ]
> # psql -U postgres
> psql (9.4beta2)
> Type "help" for help.
On Sat, Sep 20, 2014 at 1:33 PM, Josh Berkus wrote:
> For example, we could increase the estimated cost
> for an abort-early index scan by 10X, to reflect our weak confidence in
> its correctness.
Has any progress been made on the performance farm? The problem with
suggestions like this (which s
On Fri, Sep 19, 2014 at 6:58 PM, Mark Kirkwood
wrote:
> On 19/09/14 19:24, Mkrtchyan, Tigran wrote:
>>
>>
>>
>> - Original Message -
>>>
>>> From: "Mark Kirkwood"
>>> To: "Tigran Mkrtchyan"
>>> Cc: "
On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus wrote:
> Folks,
>
> Just encountered another case of critical fail for abort-early query
> plans. In this case, it will completely prevent a user from upgrading
> to 9.3; this is their most common query, and on 9.3 it takes 1000X longer.
>
> Maybe we s
On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
wrote:
>
> 9.3.5:
> 0.035940END;
>
>
> 9.4beta2:
> 0.957854END;
time being spent on 'END' is definitely suggesting i/o related issues.
This is making me very skeptical that postgres is the source of the
problem.
On Mon, Aug 18, 2014 at 4:21 PM, Matheus de Oliveira
wrote:
>
> On Mon, Aug 18, 2014 at 6:01 PM, Dave Owens wrote:
>>
>> max_locks_per_transaction = 9000
>> max_pred_locks_per_transaction = 4
performance of any query to pg_locks is proportional to the setting of
max_locks_per_transaction. s
On Wed, Jul 30, 2014 at 4:01 AM, Tomas Vondra wrote:
> On 30 Červenec 2014, 5:12, Josh Berkus wrote:
>> Explained here:
>> https://www.usenix.org/system/files/conference/fast13/fast13-final80.pdf
>>
>> 13 out of 15 tested SSD's had various kinds of corruption on a power-out.
>>
>> (thanks, Neil!)
On Mon, Jun 30, 2014 at 9:15 PM, Tom Lane wrote:
> Craig Ringer writes:
>> I was unaware that the planner made any attempt to catch users' errors
>> in marking the strictness of functions. I thought it pretty much trusted
>> the user not to lie about the mutability of functions invoked
>> indirec
On Wed, Jun 25, 2014 at 3:48 AM, Niels Kristian Schjødt
wrote:
> Hi,
> I’m running a search engine for cars. It’s backed by a postgresql 9.3
> installation.
>
> Now I’m unsure about the best approach/strategy on doing index optimization
> for the fronted search.
>
> The problem:
>
> The table co
On Thu, Jun 12, 2014 at 2:48 PM, Andreas Joseph Krogh
wrote:
>
> På torsdag 12. juni 2014 kl. 16:58:06, skrev Tom Lane :
>
> Matt Silverlock writes:
> > Hi all. This might be tricky in so much as there���s a few moving parts
> > (when isn���t there?), but I���ve tried to test the postgres side a
On Thu, Jun 12, 2014 at 9:58 AM, Tom Lane wrote:
> Matt Silverlock writes:
>> Hi all. This might be tricky in so much as there’s a few moving parts (when
>> isn’t there?), but I’ve tried to test the postgres side as much as possible.
>> Trying to work out a potential database bottleneck with a H
On Thu, Jun 5, 2014 at 6:57 PM, Vincent Lasmarias
wrote:
> Thanks for the informative responses and suggestions. My responses below:
>
> * Sorry for the double post. I posted the original message using my gmail
> account and got a "is not a member of any of the restrict_post groups"
> response and
On Thu, Jun 5, 2014 at 8:47 AM, Tom Lane wrote:
> Vince Lasmarias writes:
>> For the past few days, we've been seeing unexpected high CPU spikes in our
>> system.
>
> Recent reports have suggested that disabling transparent huge page
> management in your kernel can help with this. If the excess
On Thu, Jun 5, 2014 at 2:47 PM, Deron wrote:
> We saw very similar issues with a CentOS server with 40 cores (32
> virtualized) when moving from a physical server to a virtual server (I think
> it had 128GB RAM). Never had the problem on a physical server. We checked
> the same things as noted
On Thu, Jun 5, 2014 at 10:58 AM, Jeff Janes wrote:
> This sounds like a kernel problem, probably either the zone reclaim issue,
> or the transparent huge pages issue.
I at first thought maybe same, but I don't think THP was introduced
until 2.6.38...OP is running 2.6.32-431.11.2.el6.x86_6. Maybe
On Wed, Jun 4, 2014 at 2:58 PM, Linos wrote:
> On 04/06/14 21:36, Merlin Moncure wrote:
>> On Wed, Jun 4, 2014 at 8:56 AM, Linos wrote:
>>> Hello,
>>>
>>> Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries
>>> started performi
On Wed, Jun 4, 2014 at 8:56 AM, Linos wrote:
> Hello,
>
> Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries
> started performing a lot slower, the query I am using in this example is
> pasted here:
>
> http://pastebin.com/71DjEC21
>
>
> Considering it is a production datab
On Tue, May 13, 2014 at 6:04 PM, Dave Owens wrote:
> Hi,
>
> Apologies for resurrecting this old thread, but it seems like this is
> better than starting a new conversation.
>
> We are now running 9.1.13 and have doubled the CPU and memory. So 2x 16
> Opteron 6276 (32 cores total), and 64GB memo
On Fri, Apr 18, 2014 at 3:07 AM, Franck Routier
wrote:
> I have found the problem, using this query (found here
> http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-locks)
>
> select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
>p
On Mon, Mar 31, 2014 at 9:24 AM, Niels Kristian Schjødt <
nielskrist...@autouncle.com> wrote:
> Thanks, this seems to persist after a reboot of the server though, and I
> have never in my server's 3 months life time experienced anything like it.
>
huh. Any chance of getting 'perf' installed and
On Mon, Mar 31, 2014 at 5:25 AM, Niels Kristian Schjødt
wrote:
> I'm running postgresql 9.3 on a production server. An hour ago, out of the
> "blue", I ran into an issue I have never encountered before: my server
> started to use CPU as crazy. The server is a standard ubuntu 12.04 LTE
> install
1 - 100 of 1088 matches
Mail list logo
