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

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. The table in question is tiny -

[PERFORM] query slowdown after 9.0 -> 9.4 migration

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 scan

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

ale is... This feature 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 wrote: > On Fri, Feb 5, 2016 at 9:33 AM, Filip Re

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

OM '/tmp/test.dat' 1171ms COPY test.tab FROM '/tmp/test.dat' rows=16 1710ms COPY test.tab FROM '/tmp/test.dat' 1709ms COPY test.tab FROM '/tmp/test.dat' rows=20 2189ms COPY test.tab FROM '/tmp/test.dat' 2206

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

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 > > rows and COPY duration. > > No surprise, see AsyncExi

[PERFORM] bad COPY performance with NOTIFY in a trigger

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

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 queries

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

On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik wrote: > On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski > wrote: > >> >> 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

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

On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik 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 might be not what you r

Re: [PERFORM] problem with large inserts

gt; Just 576.909 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 id

Re: [PERFORM] problem with large inserts

Just an idea - how long does it take to run _only_ CREATE TEMP TABLE foo AS On Thu, Dec 13, 2012 at 4:37 PM, Lutz Fischer wrote: > Hi > > I have currently some trouble with inserts into a table > > INSERT INTO LPP (PPID, LID) > SELECT DISTINCT PPid, LID FROM > (SELECT * FROM PP WHERE

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

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 <> ?

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/9/26 M. D. > 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 changing settings,

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

2011/9/23 M. D. > > 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 down a bit b

Re: [PERFORM] the XID question

2011/1/19 Charles.Hou : > 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 (pgsql-performance@po

Re: [PERFORM] Auto-clustering?

(4 rows) So CLUSTER is effectively CLUSTER + REINDEX... nice. W dniu 17 grudnia 2010 10:41 użytkownik Marti Raudsepp napisał: > 2010/12/17 Filip Rembiałkowski : > > 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 >

Re: [PERFORM] Auto-clustering?

2010/12/17 Royce Ausburn > 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 slow. > > The