Re: [PERFORM] query slowdown after 9.0 -> 9.4 migration

2016-10-27 Thread Filip Rembiałkowski
On Thu, Oct 27, 2016 at 7:38 AM, Andreas Kretschmer < akretsch...@spamfence.net> wrote: > Tomas Vondra wrote: > > > > > Perhaps the table is organized / sorted differently, or something like > > that. How did you do the upgrade? > > Nothing special, dump + reload.

[PERFORM] query slowdown after 9.0 -> 9.4 migration

2016-10-26 Thread Filip Rembiałkowski
Hi. Query run time degraded after migration from Pg 9.0 + postgis 1.5 to Pg 9.4 + postgis 2.2. 1 ms versus 7 ms. Same query, same data, same schema, similar hardware. Data is small and fits in cache. EXPLAIN shows heap scan cost increase. What can be the reason for 40-fold increase in page

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

2016-02-06 Thread Filip Rembiałkowski
ature can burn a lot of CPU for nothing. I was hoping to use NOTIFY/LISTEN as superfast notification mechanism. Superfast regardless on whether you insert 100, 10k or 1m rows. On Fri, Feb 5, 2016 at 8:52 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Fri, Feb 5, 2016 at

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

2016-02-05 Thread Filip Rembiałkowski
FROM '/tmp/test.dat' rows=20 2189ms COPY test.tab FROM '/tmp/test.dat' 2206ms COPY test.tab FROM '/tmp/test.dat' On Fri, Feb 5, 2016 at 1:45 PM, Filip Rembiałkowski < filip.rembialkow...@gmail.com> wrote: > On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <t...@sss.pgh.

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

2016-02-05 Thread Filip Rembiałkowski
On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane wrote: > =?UTF-8?Q?Filip_Rembia=C5=82kowski?= > writes: > > A table has a trigger. > > The trigger sends a NOTIFY. > > Test with COPY FROM shows non-linear correlation between number of > inserted > >

[PERFORM] bad COPY performance with NOTIFY in a trigger

2016-02-04 Thread Filip Rembiałkowski
Hi. A table has a trigger. The trigger sends a NOTIFY. Test with COPY FROM shows non-linear correlation between number of inserted rows and COPY duration. Table "test.tab" Column | Type | Modifiers

[PERFORM] 9.0 performance degradation with kernel 3.11

2014-11-13 Thread Filip Rembiałkowski
Hi After upgrading our 9.0 database server from: openSUSE 11.4, kernel 2.6.37.6-24-default, Pg 9.0.13 to: openSUSE 13.1, kernel v 3.11.10-21-default, Pg 9.0.15 ... and overall server load is +1 after that. We did not add any new services/daemons. It's hard to track down to individual

Re: [PERFORM] Simple join doesn't use index

2013-01-29 Thread Filip Rembiałkowski
On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik alvinni...@gmail.com wrote: On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski plk.zu...@gmail.comwrote: do you know pgtune? it's a good tool for starters, if you want a fast postgres and don't really want to learn what's behind the scenes

Re: [PERFORM] Simple join doesn't use index

2013-01-28 Thread Filip Rembiałkowski
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik alvinni...@gmail.com wrote: It sure turned out that default settings are not a good fit. do you know pgtune? it's a good tool for starters, if you want a fast postgres and don't really want to learn what's behind the scenes. random_page_cost=1

Re: [PERFORM] problem with large inserts

2012-12-13 Thread Filip Rembiałkowski
Just an idea - how long does it take to run _only_ CREATE TEMP TABLE foo AS your SELECT here On Thu, Dec 13, 2012 at 4:37 PM, Lutz Fischer lfisc...@staffmail.ed.ac.uk wrote: Hi I have currently some trouble with inserts into a table INSERT INTO LPP (PPID, LID) SELECT DISTINCT PPid, LID

Re: [PERFORM] problem with large inserts

2012-12-13 Thread Filip Rembiałkowski
ms I don't really understand why it's working via a temp table but not directly (or in any reasonable amount of time) - but at least I have a solution I can work with. On 13/12/12 16:09, Filip Rembiałkowski wrote: Just an idea - how long does it take to run _only_ CREATE TEMP TABLE foo

Re: [PERFORM] parse - bind take more time than execute

2011-12-27 Thread Filip Rembiałkowski
hello. 1. planning time execute time, it can happen normally, for some fast-executing queries, so it is not bad per se. 2. what are your statistics settings? they influence planning time. I mean default_statistics_target and per-column SET STATISTICS? 3. upgrade to 8.4.10, it's quick upgrade

Re: [PERFORM] will the planner ever use an index when the condition is ?

2011-12-17 Thread Filip Rembiałkowski
Normally there is no chance it could work, because (a) the planner does not know all possible values of a column, and (b) btree indexes cannot search on not equal operator. BTW I've just made a case where - logically - it could work, but it still does not: create table nums ( num int4 not null,

Re: [PERFORM] slow query on tables with new columns added.

2011-09-26 Thread Filip Rembiałkowski
2011/9/26 M. D. li...@turnkey.bz I have full access to the database, but no access to the application source code. If creating an index will help, I can do that, but with the columns I don't see it helping as I don't have access to the application source to change that. So yes, by

Re: [PERFORM] slow query on tables with new columns added.

2011-09-24 Thread Filip Rembiałkowski
2011/9/23 M. D. li...@turnkey.bz I did a software upgrade, and with it came a new feature where when selecting a customer it queries for the sum of a few columns. This takes 7 seconds for the 'Cash Sale' customer - by far the most active customer. I'd like to see if it's possible to get it

Re: [PERFORM] the XID question

2011-01-19 Thread Filip Rembiałkowski
2011/1/19 Charles.Hou giveme...@gmail.com: what's the definetion of XID? XID == Transaction ID. select * from mybook SQL command also increase the XID ? Yes. Single SELECT is a transaction. Hence, it needs a transaction ID. greets, Filip -- Sent via pgsql-performance mailing list

Re: [PERFORM] Auto-clustering?

2010-12-17 Thread Filip Rembiałkowski
2010/12/17 Royce Ausburn ro...@inomial.com Hi all, I have a table that in the typical case holds two minute sample data for a few thousand sources. Often we need to report on these data for a particular source over a particular time period and we're finding this query tends to get a bit

Re: [PERFORM] Auto-clustering?

2010-12-17 Thread Filip Rembiałkowski
...@juffo.orgnapisał: 2010/12/17 Filip Rembiałkowski filip.rembialkow...@gmail.com: regarding clustering: it does not help with index bloat. I'm almost sure it does, CLUSTER re-creates all indexes from scratch after copying the tuples. Regards, Marti