On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C
wrote:
> First output show the output when the query is executed from sql command
> line. The second output show when it is executed from the application. AS
> per the output it is clear that the when the query is executed through JDBC
> its not usin
On Tue, Sep 19, 2017 at 7:31 PM, monika yadav
wrote:
> Hi All,
>
> I didn't understand why same sub plan for the sub query executed two
> times? As per the query it should have been executed only once.
>
> Can someone please explain this behaviour of query execution ?
>
The sum_bid at the end o
I have a complicated query which runs the exact same subplan more than once.
Here is a greatly simplified (and rather pointless) query to replicate the
issue:
select aid, sum_bid from
(select
aid,
(select sum(bid) from pgbench_branches
where bbalance between -1
On Mon, Sep 4, 2017 at 1:14 AM, 우성민 wrote:
> Hi team,
>
> I'm trying to configure postgres and pgbouncer to handle many inserts from
> many connections.
>
> Here's some details about what i want to achieve :
>
> We have more than 3000 client connections, and my server program forks
> backend pr
On Sat, Aug 19, 2017 at 10:37 AM, anand086 wrote:
Your email is very hard to read, the formatting and line wrapping is
heavily mangled. You might want to attach the plans as files attachments
instead of or in addition to putting the in the body.
> -> Index Only Scan using ui_nkey_test_tab on
On Tue, Aug 15, 2017 at 3:06 AM, Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hi,
> So I I run the cheks that jeff mentioned :
> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour
> and 35 minutes
> \copy local_postresql_table from /tmp/tmp with binary - Didnt
On Mon, Aug 14, 2017 at 6:24 AM, Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> I have performance issues with two big tables. Those tables are located on
> an oracle remote database. I'm running the quert : insert into
> local_postgresql_table select * from oracle_remote_table.
>
> The
On Tue, Aug 8, 2017 at 8:20 PM, l...@laurent-hasson.com <
l...@laurent-hasson.com> wrote:
> Hello,
>
>
> We have a fairly large static dataset that we load into Postgres. We made
> the tables UNLOGGED and saw a pretty significant performance improvement
> for the loading. This was all fantastic un
On Wed, Jul 12, 2017 at 3:04 AM, Charles Nadeau
wrote:
> Jeff,
>
> Here are the 2 EXPLAINs for one of my simplest query:
>
It looks like dstexterne and flowcompact are both views over flow. Can you
share the definition of those views?
I think the iowait > 12.5% is due to the parallel query ex
On Tue, Jul 11, 2017 at 4:42 PM, Joshua D. Drake
wrote:
> On 07/11/2017 04:15 PM, Merlin Moncure wrote:
>
>> 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
>>>
On Tue, Jul 11, 2017 at 4:02 AM, Charles Nadeau
wrote:
> Jeff,
>
> I used fio in a quick benchmarking script inspired by https://smcleod.net/
> benchmarking-io/:
>
> #!/bin/bash
> #Random throughput
> echo "Random throughput"
> sync
> fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=
On Mon, Jul 10, 2017 at 7:03 AM, Charles Nadeau
wrote:
>
> The problem I have is very poor read. When I benchmark my array with fio I
> get random reads of about 200MB/s and 1100IOPS and sequential reads of
> about 286MB/s and 21000IPS.
>
That doesn't seem right. Sequential is only 43% faster?
On Thu, Jun 29, 2017 at 12:11 PM, Yevhenii Kurtov wrote:
> Hi Jeff,
>
> That is just a sample data, we are going live in Jun and I don't have
> anything real so far. Right now it's 9.6 and it will be a latest stable
> available release on the date that we go live.
>
You need to use your knowled
On Tue, Jun 27, 2017 at 11:47 PM, Yevhenii Kurtov wrote:
> Hello,
>
> We have a query that is run almost each second and it's very important to
> squeeze every other ms out of it. The query is:
>
> SELECT c0."id" FROM "campaign_jobs" AS c0
> WHERE (((c0."status" = $1) AND NOT (c0."id" = ANY($2)))
On Fri, Jun 23, 2017 at 1:09 PM, Chris Wilson
wrote:
>
> The records can already be read in order from idx_metric_value If this
> was selected as the primary table, and metric_pos was joined to it, then
> the output would also be in order, and no sort would be needed.
>
> We should be able to
On Fri, Jun 9, 2017 at 3:43 PM, Michael Paquier
wrote:
> On Fri, Jun 9, 2017 at 10:55 PM, Cocco Gianfranco
> wrote:
> > Is there a way to fix “wal_segsize” to about 1 Gb in 9.2. version, and
> “rebuild” postgreSQL server?
>
> As long as you are able to compile your own version of Postgres and
>
On Fri, Jun 9, 2017 at 6:04 AM, Frits Jalvingh wrote:
>
> I already changed the following config parameters:
> work_mem 512MB
> synchronous_commit off
>
Since you are already batching up commits into large chunks, this setting
is not very useful, but does risk you losing supposedly-committed dat
On Sat, May 27, 2017 at 1:40 AM, 梁海安（Killua Leung） <
lianghaian...@pingan.com.cn> wrote:
> Hi team:
>
>The following SQL is very slow in 9.6.1 for the plan has a “sort”
> node.
>
The difference is only a factor of 2. I wouldn't call it "very" slow.
Your explain plans are unreadable, pl
On Tue, May 23, 2017 at 4:03 AM, Gunnar "Nick" Bluth <
gunnar.bluth.ext...@elster.de> wrote:
> Am 05/22/2017 um 09:57 PM schrieb Jeff Janes:
> >
> > create view view2 as select id,
> > (
> > select md5 from thing_alias where thing_id=id
> >
I need to make a view which decorates rows from a parent table with
aggregated values from a child table. I can think of two ways to write it,
one which aggregates the child table to make a new query table and joins
the parent to that, as shown in "view1" below. Or does subselect in the
select li
I'm spoiled by using pg_stat_statements to find the hotspot queries which
could use some attention.
But with some recent work, all of the hotspots are of the form "FETCH 1000
FROM c3". The vast majority of the queries return less than 1000 rows, so
only one fetch is issued per execution.
Is ther
On Mon, May 15, 2017 at 3:22 PM, Tom Lane wrote:
> Jeff Janes writes:
> > I've tried versions 9.6.3 and 10dev, and neither do what I expected. It
> > doesn't seem to be a planning problem where it thinks the fast plan is
> > slower, it just doesn't seem t
I need to do a join between two foreign tables using columns of different
types.
select data from remote2 join remote1 on ((remote2.id)::bigint=remote1.id)
where cutoff > 0.;
For demonstration purposes, I use a loop-back foreign server, set up in the
attached sql file.
If I do the join direc
On Tue, Mar 7, 2017 at 6:26 PM, Pat Maddox wrote:
> Hi there,
>
> I’ve been asked to help with a project dealing with slow queries. I’m
> brand new to the project, so I have very little context. I’ve gathered as
> much information as I can.
>
> I’ve put the schema, query, and explain info in gist
On Mon, Mar 6, 2017 at 8:46 AM, twoflower wrote:
> Thank you Jeff.
>
> There are 7 million rows satisfying fk_id_client = 20045. There is an
> index on fk_id_client, now I added a composite (fk_id_client, id) index but
> that did not help.
>
With 7 million rows, you shouldn't expect any magic he
On Mon, Mar 6, 2017 at 6:22 AM, twoflower wrote:
> I have the following query
>
> select *
> from "JOB_MEMORY_STORAGE" st
> inner join "JOB_MEMORY" s on s.fk_id_storage = st.id
> where st.fk_id_client = 20045
> order by s.id asc limit 50
>
>
The query stops as soon as it finds 50 rows which mee
On Thu, Mar 2, 2017 at 1:19 PM, Sven R. Kunze wrote:
> On 01.03.2017 18:04, Jeff Janes wrote:
>
> On Wed, Mar 1, 2017 at 6:02 AM, Sven R. Kunze wrote:
>
>> On 28.02.2017 17:49, Jeff Janes wrote:
>>
>> Oh. In my hands, it works very well. I get 70 seconds to do th
On Fri, Mar 3, 2017 at 4:44 AM, Dinesh Chandra 12108 <
dinesh.chan...@cyient.com> wrote:
> Dear Nur,
>
>
>
> The below is the output for psql=> EXPLAIN ANALYZE SELECT DISTINCT
> feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence
> oe ON p.feature_id = oe.evd_feature_id WHERE
On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta
> wrote:
>
>> plain analyze
>> select tmp_san_1.id
>> from tmp_san_1
>>left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text
>> where t
On Wed, Mar 1, 2017 at 6:02 AM, Sven R. Kunze wrote:
> On 28.02.2017 17:49, Jeff Janes wrote:
>
> Oh. In my hands, it works very well. I get 70 seconds to do the {age:
> 20} query from pure cold caches, versus 1.4 seconds from cold caches which
> was followed by pg_prewarm(&
On Tue, Feb 28, 2017 at 12:27 AM, Sven R. Kunze wrote:
> On 27.02.2017 19:22, Jeff Janes wrote:
>
> If by 'permanently', you mean even when you intentionally break things,
> then no. You will always be able to intentionally break things. There is
> on-going disc
On Sun, Feb 26, 2017 at 5:28 AM, Sven R. Kunze wrote:
>
>
> Using "select pg_prewarm('docs');" and on any of the indexes doesn't help
> either.
> After a "systemctl stop postgresql.service && sync && echo 3 >
> /proc/sys/vm/drop_caches && systemctl start postgresql.service" the age=20,
> 30 or na
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 following a 9.6 in place upgrade from 9.2. By poor I mean
> 7x slower. Problem was resolved b
On Mon, Jan 23, 2017 at 9:43 AM, Simon Riggs wrote:
> On 23 January 2017 at 17:12, Jeff Janes wrote:
>
> >> Just to make sure anyone reading the mailing list archives isn't
> >> confused, running pg_start_backup does *not* make PG stop writing to
> >> BA
On Mon, Jan 23, 2017 at 7:28 AM, Jim Nasby wrote:
> On 1/22/17 11:32 AM, Stephen Frost wrote:
>
>> The 1-second window concern is regarding the validity of a subsequent
>> incremental backup.
>>
>
> BTW, there's a simpler scenario here:
>
> Postgres touches file.
> rsync notices file has differen
On Sun, Jan 22, 2017 at 6:57 AM, Stephen Frost wrote:
> Greetings,
>
> * julyanto SUTANDANG (julya...@equnix.co.id) wrote:
> > CORRECTION:
> >
> > "you might you pg_start_backup to tell the server not to write into the
> > DATADIR"
> >
> > become
> >
> > "you might *use* pg_start_backup to tell t
> big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]';
> I wonder why bitmap heap scan adds such a big amount of time on top of the
> plain bitmap index scan.
> It seems to me, that the recheck is active although all blocks are exact
> [1] and that pg is loading the jsonb for
On Mon, Nov 14, 2016 at 3:45 AM, Pietro Pugni
wrote:
>
> The first issue I faced was about maintenance_work_mem because I set it to
> 16GB and the server silently crashed during a VACUUM because I didn’t
> consider that it could take up to autovacuum_max_workers *
> maintenance_work_mem (roughly
On Mon, Nov 14, 2016 at 4:01 AM, Seckin Pulatkan
wrote:
> Hi,
>
> On our production environment (PostgreSQL 9.4.5 on
> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
> 4.8.5-4), 64-bit), one of our queries runs very slow, about 5 minutes . We
> noticed that it does not us
On Fri, Nov 11, 2016 at 7:19 AM, Henrik Ekenberg wrote:
> Hi,
>
> I have a select moving around a lot of data and takes times
> Any advice tuning this query ?
>
> EXPLAIN (ANALYZE ON, BUFFERS ON)
>
When accessing lots of data, sometimes the act of collecting timing on all
of the actions makes th
On Thu, Nov 10, 2016 at 10:54 PM, l...@laurent-hasson.com <
l...@laurent-hasson.com> wrote:
> Hello,
>
>
>
> I am trying to implement an efficient “like” over a text[]. I see a lot of
> people have tried before me and I learnt a lot through the forums.
>
Have you looked at parray_gin?
https://gi
On Wed, Nov 2, 2016 at 12:06 PM, Joao Junior wrote:
> Hi friends,
>
> I am running 2 Linux machines, kernel 3.13.0-45-generic #74-Ubuntu SMP.
> Postgresql version 9.4 in both machine, in a Hot Standby cenario.
>
> Master-Slave using WAL files, not streaming replication.
>
> The archive_command f
On Thu, Sep 29, 2016 at 4:11 AM, Alex Ignatov (postgrespro) <
a.igna...@postgrespro.ru> wrote:
>
>
> *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
> ow...@postgresql.org] *On Behalf Of *
>
> Thank you Terry. You get the gold star. :) I was waiting for that to
> come
On Thu, Sep 29, 2016 at 11:12 AM, Pavel Stehule
wrote:
>
>
> 2016-09-29 14:20 GMT+02:00 Sven R. Kunze :
>
>> On 23.09.2016 11:00, Pavel Stehule wrote:
>>
>> 2016-09-23 8:35 GMT+02:00 Sven R. Kunze :
>>
>>> I was wondering: would it be possible for PostgreSQL to rewrite the
>>> query to generate t
On Thu, Sep 29, 2016 at 11:48 AM, Sven R. Kunze wrote:
> On 29.09.2016 20:03, Jeff Janes wrote:
>
> Perhaps some future version of PostgreSQL could do so, but my gut feeling
> is that that is not very likely. It would take a lot of work, would risk
> breaking or slowing down ot
On Thu, Sep 22, 2016 at 11:35 PM, Sven R. Kunze wrote:
> Thanks a lot Madusudanan, Igor, Lutz and Jeff for your suggestions.
>
> What I can confirm is that the UNION ideas runs extremely fast (don't have
> access to the db right now to test the subquery idea, but will check next
> week as I trave
On Mon, Sep 26, 2016 at 5:53 AM, Greg Spiegelberg
wrote:
>
>>
>> I may need to understand autovacuum better. My impression was it
> consulted statistics and performed vacuums one table at a time based on the
> vacuum threshold formula on https://www.postgresql.org/
> docs/9.5/static/routine-vac
On Sun, Sep 25, 2016 at 7:50 PM, Greg Spiegelberg
wrote:
> Hey all,
>
> Obviously everyone who's been in PostgreSQL or almost any RDBMS for a time
> has said not to have millions of tables. I too have long believed it until
> recently.
>
> AWS d2.8xlarge instance with 9.5 is my test rig using XF
On Fri, Sep 23, 2016 at 3:12 AM, Dev Nop wrote:
> I’m storing thousands of independent documents each containing around 20k
> rows. The larger the document, the more likely it is to be active with
> inserts and updates (1000s/day). The most common read query is to get all
> the rows for a single
On Thu, Sep 22, 2016 at 6:37 AM, Madusudanan.B.N
wrote:
> > However, this results in an awful slow plan (requiring to scan the
> complete big_table which obviously isn't optimal)
>
> You mean to say there is a sequential scan ? An explain would be helpful.
> Are there indexes on the provided wher
On Sat, Sep 10, 2016 at 3:49 AM, Samir Magar wrote:
> Hello,
>
>
> My Application has normally 25 to 30 connections and it is doing lot of
> insert/update/delete operation.
> The database size is 100GB.
> iowait is at 40% to 45 % and CPU idle time is at 45% to 50%
> TOTAL RAM = 8 GB TOTAL CPU
On Fri, Aug 26, 2016 at 6:17 AM, Tommi K wrote:
> Hello,
> thanks for the response. I did not get the response to my email even
> though I am subscribed to the pgsql-performance mail list. Let's hope that
> I get the next one :)
>
> Increasing work_mem did not have great impact on the performance
On Sat, Aug 27, 2016 at 7:13 AM, Craig James wrote:
> On Fri, Aug 26, 2016 at 9:11 PM, Jim Nasby
> wrote:
>
>> On 8/26/16 3:26 PM, Mike Sofen wrote:
>>
>>> Is there way to keep query time constant as the database size grows.
>>>
>>
>> No. More data == more time. Unless you find a way to break th
On Thu, Aug 18, 2016 at 11:55 AM, Victor Yegorov wrote:
> 2016-08-18 18:59 GMT+03:00 Jeff Janes :
>>
>> Both plans touch the same pages. The index scan just touches some of
>> those pages over and over again. A large setting of
>> effective_cache_size would tell
On Thu, Aug 18, 2016 at 6:52 AM, Victor Yegorov wrote:
> Greetings.
>
> I have a question on why planner chooses `IndexScan` for the following
> query:
>
> SELECT la.loan_id, la.due_date, la.is_current
> FROM loan_agreements la WHERE la.is_current AND '2016-08-11' >
> la.due_date;
>
...
On Tue, Jun 28, 2016 at 6:24 PM, wrote:
>
>
> PostgreSQL version:
> PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit
The current minor version of that branch is 9.3.13, so you are 9 bug
fix releases behind.
I don't know if this matters, because I see that my first guess of
your probl
On Wed, Jun 29, 2016 at 3:00 AM, Niels Kristian Schjødt
wrote:
> About a day ago, there seems to have been some trouble in the network of my
> database (postgresql 9.3).
>
> I’m running my db with a streaming replication setup with wall shipping.
>
> I sync wal logs to a mounted networkdrive using
On Sat, Jun 25, 2016 at 9:19 AM, t.dalpo...@gmail.com
wrote:
> Hi,
> I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64 bit,
> same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
> I've in each partition a simple database with one table called data256 with
> one
On Wed, Jun 22, 2016 at 9:36 PM, Craig James wrote:
> On Wed, Jun 22, 2016 at 11:36 AM, Jeff Janes wrote:
>> You might be able to build a multiple column index on (smiles,
>> version_id) and have it do the right thing automatically. Whether that
>> is possible, and if so
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 disclosed?
> Notice that it doesn't use the i_version_smiles index at all, but instead
> applie
On Fri, Jun 17, 2016 at 8:19 AM, wrote:
> Hi ,
>
> I am connecting to PostgreSQL 9.4 via an ODBC driver on Windows machine from
> MS VBA application. I am facing huge performance issues while inserting data
> continuously. On analysing the logs , there were around 9 statements
> related to Sa
On Wed, Jun 15, 2016 at 6:16 AM, k...@rice.edu wrote:
> On Wed, Jun 15, 2016 at 03:09:04PM +0200, Ivan Voras wrote:
>> On 15 June 2016 at 15:03, k...@rice.edu wrote:
>>
>>
>> I don't suppose there's an effort in progress to make hash indexes use WAL?
>> :D
>
> Hi Ivan,
>
> Several people have loo
On Tue, Jun 7, 2016 at 9:57 PM, Ed Felstein wrote:
> Hello,
> First time poster here. Bear with me.
> Using PostgreSQL 9.5
> I have a situation where I have a LIKE and a NOT LIKE in the same query to
> identify strings in a varchar field. Since I am using wildcards, I have
> created a GIN index
On Sun, Jun 5, 2016 at 9:03 AM, Tom Lane wrote:
> Claudio Freire writes:
>> So correlated index scans look extra favourable vs bitmap index scans
>> because bitmap heap scans consider random page costs sans correlation
>> effects (even though correlation applies to bitmap heap scans as
>> well).
On Mon, May 30, 2016 at 10:53 AM, Volker Boehm wrote:
> The reason for using the similarity function in place of the '%'-operator is
> that I want to use different similarity values in one query:
>
> select name, street, zip, city
> from addresses
> where name % $1
> and stree
On Sat, May 28, 2016 at 5:38 PM, Jake Magner wrote:
> Tom Lane-2 wrote
>> Jake Magner <
>
>> jakemagner90@
>
>> > writes:
>>> I tried without doing an INSERT at all, just running the SELECT queries
>>> and
>>> the result is the same. Nested loop is chosen but is much slower.
>>
>> FWIW, I just not
On Tue, May 24, 2016 at 10:39 AM, Justin Pryzby wrote:
> Summary: Non-unique btree indices are returning CTIDs for rows with same
> value of indexed column not in logical order, imposing a high performance
> penalty.
>
> Running PG 9.5.3 now, we have a time-based partitions of append-only tables
>
On Thu, May 12, 2016 at 8:13 AM, Robert Klemme
wrote:
> 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...
>>
>
On Wed, Apr 20, 2016 at 11:54 AM, Teodor Sigaev wrote:
>>
>> The obvious thing seems to make a table with ~100 columns, with 1 column
>> for each boolean property. Though, what type of indexing strategy would
>> one use on that table? Doesn't make sense to do BTREE. Is there a better
>> way to str
On Wed, Apr 20, 2016 at 11:41 AM, Rob Imig wrote:
> Hey all,
>
> New to the lists so please let me know if this isn't the right place for
> this question.
>
> I am trying to understand how to structure a table to allow for optimal
> performance on retrieval. The data will not change frequently so
On Tue, Apr 19, 2016 at 1:07 AM, Aldo Sarmiento wrote:
> Hello,
>
> I'm assuming this topic has probably been bludgeoned to a pulp, but my
> google-fu can't seem to find a solution.
>
> I have two relatively largish tables that I'm trying to join that result in
> a slow query.
>
> Hardware:
>
> 20
On Tue, Mar 22, 2016 at 9:41 AM, Oleg Bartunov wrote:
>
>
> On Sat, Mar 19, 2016 at 5:44 AM, Jeff Janes wrote:
>>
>>
>> I don't see why it would not be possible to create a new execution node
>> type that does an index scan to obtain order (or just to sati
On Sun, Mar 27, 2016 at 9:12 AM, Wei Shan wrote:
> Hi Andreas,
>
> The tablespace is not on SSD although I intend to do it within the next
> week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
> help.
Setting random_page_cost to less than seq_page_cost is nonsensical.
You
On Fri, Mar 18, 2016 at 6:26 AM, Jan Bauer Nielsen wrote:
> Hi,
>
> While developing a batch processing platform using postgresql as the
> underlying data store we are seeing a performance decline in our
> application.
>
> In this application a job is broken up into chunks where each chunk contain
On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh
wrote:
> På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane :
>
> Andreas Joseph Krogh writes:
> > 1. Why isnt' folder_id part of the index-cond?
>
> Because a GIN index is useless for sorting.
>
> > 2. Is there a way to make it use the (sa
On Sat, Feb 20, 2016 at 8:46 AM, tuanhoanganh wrote:
> Hello
>
> I have a bad query on PostgreSQL 9.0.23 - 64bit - Windows 2012 R2 - 48GB Ram
9.0 is no longer supported. You should work toward upgrading to a
newer version. It might not solve this problem, but it would give you
better tools for
On Tue, Feb 9, 2016 at 4:09 PM, Dan Langille wrote:
> I have a wee database server which regularly tries to insert 1.5 million or
> even 15 million new rows into a 400 million row table. Sometimes these
> inserts take hours.
>
> The actual query to produces the join is fast. It's the insert whic
On Mon, Feb 8, 2016 at 2:21 AM, Marc Mamin wrote:
>
> - auto vacuum will not run as these are insert only tables
> - according to this post, auto analyze would also do the job:
>
> http://postgresql.nabble.com/Performance-problem-with-gin-index-td5867870.html
> It seems that this information
On Thu, Feb 4, 2016 at 9:19 AM, Jordi wrote:
The custom here is to respond in line, not to top-post. Thanks.
>
> So basically you're saying it's hard to do sorting in any way when a gin
> index is involved? Neither with a complete multi-column btree_gin index
> because it doesn't support sortin
On Sat, Jan 9, 2016 at 9:57 PM, Jinhua Luo wrote:
>
> To make a clean test env, I clone a new table, removing the indexes (keeping
> the primary key) and triggers, and use pgbench to test insert statement
> purely.
Can you share the pgbench command line, and the sql file you feed to
it (and whate
On Thu, Dec 10, 2015 at 8:38 PM, ankur_adwyze wrote:
> Hi Folks,
>
> I am a newbie to this mailing list. Tried searching the forum but didn't
> find something similar to the problem I am facing.
>
> Background:
> I have a Rails app with Postgres db. For certain reports, I have to join
> multiple t
On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astill wrote:
>
>
>
>
> If I create the index show+best+block+row+seat then the planner appears to
> favour that, and all is well. Despite the startup cost estimate being the
> same, and total cost being 0.01 higher. This is something I fail to
> understa
On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astill wrote:
> Hi All,
>
> Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and
> trying to figure out why a particular index is being chosen over another for
> updates/deletes.
>
> From what I can see the reason is that plans using e
On Thu, Nov 5, 2015 at 1:11 AM, Artem Tomyuk wrote:
> Hi all.
>
> Is the speed of hash operations stands on the performance of CPU?
Yes, but the variation is probably not as much as the raw timing in
your example indicates.
> Below you can see part from output of explain analyze command
>
> Inte
On Mon, Nov 2, 2015 at 12:19 AM, Andrey Osenenko
wrote:
>
> It also looks like if there was a way to create a table with just primary
> key and add an index to it that indexes data from another table, it would
> work much, much faster since there would be very little to read from disk
> after inde
On Sun, Nov 1, 2015 at 10:52 PM, Andrey Osenenko
wrote:
> I have a table with roughly 300 000 rows, each row containing two large
> strings - one is news article HTML, the other is news article plaintext. The
> table has a bigint primary key.
>
> A GIN index is used to do fulltext search on the pl
On Fri, Oct 23, 2015 at 9:26 AM, David Osborne wrote:
> Ah yes sorry:
>
> I think these cover it...
>
> CREATE AGGREGATE sum (
> sfunc = array_add,
> basetype = INTEGER[],
> stype = INTEGER[],
> initcond = '{}'
>);
>
> CREATE OR REPLACE FUNCTION array_add(int[],int[]) R
On Fri, Oct 23, 2015 at 7:29 AM, 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.
>
Postgres does not ship with any 'sum' function which take
On Mon, Oct 12, 2015 at 11:17 AM, Shaun Thomas wrote:
> Hi guys,
>
> I've been doing some design investigation and ran into an interesting snag
> I didn't expect to find on 9.4 (and earlier). I wrote a quick python script
> to fork multiple simultaneous COPY commands to several separate tables an
On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <
bertrand.paq...@doctolib.fr> wrote:
> Hi,
>
> We have got big slow down on our production plateform (PG 9.4.4).
>
What is it slow compared to? Did your version change, or your
workload/usage change?
>
> After analyzing wals with pg_xlogdump, w
On Sat, Sep 26, 2015 at 11:06 PM, Guillaume Lelarge
wrote:
> Le 27 sept. 2015 8:02 AM, "Guillaume Lelarge" a
> écrit :
> >
> > Le 26 sept. 2015 6:26 PM, "Adam Scott" a
> écrit :
> > >
> > > How do we measure queries per second (QPS), not transactions per
> second, in PostgreSQL without turning
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_stringCOALESCE((vorname)::text,
> ''::text) || ' '::text) || (name1)::text))::character varying,
> (-1)))::text || ' '::text) || (nor
On Wed, Aug 26, 2015 at 4:29 AM, Henrik Thostrup Jensen
wrote:
> On Wed, 26 Aug 2015, Emre Hasegeli wrote:
>
> Are the coverage operatons just that expensive?
>>>
>>
>> They shouldn't be. A similar query like yours works in 0.5 second on my
>> laptop:
>>
> [snip]
>
> I get the same from your tes
On Mon, Aug 24, 2015 at 8:18 AM, Guo, Yun wrote:
>
>
> From: Jeff Janes
> Date: Friday, August 21, 2015 at 10:44 PM
> To: Yun
> Subject: Re: [PERFORM] query not using GIN index
>
> On Fri, Aug 21, 2015 at 6:55 PM, Guo, Yun wrote:
>
>> Hi,
>>
>> We
On Fri, Aug 14, 2015 at 9:54 AM, Jeff Janes wrote:
> On Fri, Aug 14, 2015 at 9:34 AM, Josh Berkus wrote:
>
>>
>> On 08/13/2015 01:59 PM, Jeff Janes wrote: execute on the
>>
>> > Once the commit of the whole-table update has replayed, the problem
>> &g
On Fri, Aug 14, 2015 at 9:34 AM, Josh Berkus wrote:
>
> On 08/13/2015 01:59 PM, Jeff Janes wrote: execute on the
>
> > Once the commit of the whole-table update has replayed, the problem
> > should go way instantly because at that point each backend doing the
> >
On Thu, Aug 13, 2015 at 10:09 AM, Josh Berkus wrote:
> Setup:
>
> * PostgreSQL 9.3.9
> * 1 master, 1 replica
> * Tiny database, under 0.5GB, completely cached in shared_buffers
> * 90% read query traffic, which is handled by replica
> * Traffic in the 1000's QPS.
>
> The wierdness:
>
> Periodical
On Sat, Jul 25, 2015 at 7:50 AM, Craig James wrote:
> 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 modu
On Fri, Jul 24, 2015 at 2:40 PM, Laurent Debacker
wrote:
The Recheck Cond line is a plan-time piece of info, not a run-time piece.
> It only tells you what condition is going to be rechecked if a recheck is
> found to be necessary.
Thanks Jeff! That makes sense indeed.
>
> I'm a bit surprised
On Thu, Jul 23, 2015 at 9:58 AM, Laurent Debacker
wrote:
> Hi,
>
> I have read that GIN indexes don't require a recheck cond for full text
> search as long as work_mem is big enough, otherwise you get lossy blocks,
> and the recheck cond.
>
> In my case, I have no lossy blocks (from what I could
1 - 100 of 501 matches
Mail list logo
