Re: [PERFORM] function execute on v.9.2 slow down

it eventually start running faster? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Effect of the WindowAgg on the Nested Loop

, and therefore it happens 10x instead of 1x. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1

On Mon, May 13, 2013 at 4:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, May 13, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: You know, of course, that the join size estimate isn't arrived at that way. Still, this point does make it seem

Re: [PERFORM] Lock and pg_stat

On Mon, May 13, 2013 at 9:05 AM, Desbiens, Eric edesbi...@lxdata.com wrote: I tried also: select * from pg_class where oid=30352481; but didn't got anything You probably want where relfilenode=30352481. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [PERFORM] Setting vacuum_freeze_min_age really low

. Of course, in those cases we won't be able to freeze, either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1

for the join product couldn't be more than 33 * 1 = 33 rows, but the planner is estimating 62335 rows, which seems like nonsense. The actual result cardinality is 23. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing

Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1

On Mon, May 13, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: The planner is estimating this the outer side of this nested loop will produce 33 rows and that the inner side will produce 1. One would assume that the row estimate for the join

Re: [PERFORM] PostgreSQL planner

=12111 width=0) (actual time=4.372..4.372 rows=12038 loops=1) Index Cond: (thing_id = t1.thing_id) Total runtime: 72.461 ms The difference is that this query has only one column in its target list, not *. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Setting vacuum_freeze_min_age really low

vacuum_freeze_table_age. Basically, I would guess that both the costs and the benefits of changing this are pretty small. It would be nice to hear from someone who has tried it, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance

Re: [PERFORM] Setting Statistics on Functional Indexes

. Shouldn't there be a separate estimator for scalarlesel? Or should the existing estimator be adjusted to handle the two cases differently? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [HACKERS] [PERFORM] out of memory

or more. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

On Sun, Sep 2, 2012 at 5:39 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Thu, Aug 30, 2012 at 8:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Aug 30, 2012 at 4:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

are still screwed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgres clustering interactions with pg_dump

to look at pg_stats.correlation for the clustered column - that's often a good way to know whether things are ordered the way you expect, and it's updated every time the table is analyzed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql

Re: [PERFORM] High CPU Usage

on this which can help to reduce IO without affecting major performance -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Array fundamentals

(ARRAY['abba', 'queen', 'New string to add'])) It sounds like one or both of your functions have a bug in them, but without knowing what they're supposed to do or seeing the source code, it's pretty hard to guess what it might be. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Sequencial scan in a JOIN

-scans on a_activity rather than just 4. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

we'd need to see how much that erodes the benefit, but we've certainly done back-branch rearrangements in pg_dump in the past to fix various kinds of issues, and this is pretty non-invasive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

On Thu, May 31, 2012 at 10:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, May 31, 2012 at 10:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not; Jeff Janes is.  But you shouldn't be holding your breath anyway, since it's 9.3 material at this point

Re: [PERFORM] heavly load system spec

seen, but it's close. What hardware is Oracle running on? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Sudden Query slowdown on our Postgresql Server

to context-switch out - i.e. block - and therefore find out what lock and call path is contended. LWLocks don't show up in pg_locks, so you can't troubleshoot this sort of contention that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] Performance of SQL Function versus View

turn outs to be a win. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] ...WHERE TRUE condition in union results in bad query pla

a join. While that could * be fixed with a more complex data structure, at present there's not much * point because no improvement in the plan could result. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list

Re: [PERFORM] set autovacuum=off

'. I have no idea what I should be looking for here. If you have lock contention, you'll see locks with granted='f', at least from time to time. Those are the ones you want to worry about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] text search: tablescan cost for a tsvector

And here: http://archives.postgresql.org/message-id/CANxtv6XiuiqEkXRJU2vk=xkafxrlep7uvhgr-xmcyjgqz29...@mail.gmail.com The problem seems to be that the cost estimator doesn't know that detoasting is expensive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [PERFORM] How to improve insert speed with index on text column

if you create the index using COLLATE C? Assuming you're on 9.1.x... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Vacuuming problems on TOAST table

it does. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pl/pgsql functions outperforming sql ones?

evaluation), or even if you manage to avoid that, the system can inline things in multiple places and produce the same effect. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Slow nested loop execution on larger server

calls, but if there's other activity on the system you might get something like this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] wal_level=archive gives better performance than minimal - why?

. It might be worth compiling with POSIX_FADV_DONTNEED undefined and see whether that changes anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] How to remove a table statistics ?

which is fully computed only during the index creation. Look for rows where starelid is equal to the OID of the index. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] pg_upgrade failure contrib issue?

it easily, but since everything's been redirected to /dev/null, you can't. I believe that this gets considerably better if you run pg_upgrade with the -l logfile option, and then check the log file. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] Subquery flattening causing sequential scan

wouldn't need to bounce the production server to test that. You could just use SET in the session you were testing from. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] partitioned table: differents plans, slow on some situations

of repeatedly index-scanning, why not use a hash join instead of a nested loop? That seems likely to be a whole lot faster for the 445 rows the planner is estimating. Can you show us all of your non-default configuration settings? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Autovacuum Issue

decision about disable autovacuum for my application. I am planning to run vacuum command daily on that small table which has frequent updates. Sounds like a bad plan. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing

Re: [PERFORM] Query planner suggestion, for indexes with similar but not exact ordering.

that small segment, as soon as the partnum increments when walking the index, the buffer zeros out again for next sort group. This has come up before and seems worthwhile, but nobody's implemented it yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] Problems with FTS

random_page_cost=0.5; SET seq_page_cost=0.3 and see if those settings help. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Strange query plan

how you slice it. Unless I'm misreading this, it's actually taking only about 4 microseconds per row, which does not obviously suck. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Blocking excessively in FOR UPDATE

to happen asynchronously for a while, but then when you get too much dirty data in the cache, it starts blocking. The only thing I'm fuzzy about is why it's locking so many rows, given that the output says rows=1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [PERFORM] Strange query plan

to be anything like instantaneous. On the flip side, if I *am* misreading the output and the number of rows needed to compute the aggregate is actually some very small number, then you ought to be getting an index scan even in older versions. -- Robert Haas EnterpriseDB: http

Re: [PERFORM] Blocking excessively in FOR UPDATE

. That doesn't seem like a good idea. I would start with the default and tune down. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] function slower than the same code in an sql file

On Fri, Oct 28, 2011 at 9:39 AM, CS DBA cs_...@consistentstate.com wrote: No parameters,  one of them looks like this: [ code snippet ] It's hard to believe this is the real code, because SELECT without INTO will bomb out inside a PL/pgsql function, won't it? -- Robert Haas EnterpriseDB

Re: [PERFORM] Query running a lot faster with enable_nestloop=false

here - factor out sections of the query that are referenced multiple times, like the join between sales_order_items and invoices, and create a temporary table. ANALYZE it, and then use it to run the main query. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [PERFORM] function slower than the same code in an sql file

On Thu, Nov 3, 2011 at 11:31 AM, Rodrigo Gonzalez rjgonz...@estrads.com.ar wrote: El 03/11/11 11:42, Robert Haas escribió: On Fri, Oct 28, 2011 at 9:39 AM, CS DBA cs_...@consistentstate.com wrote: No parameters,  one of them looks like this: [ code snippet ] It's hard to believe

Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

-and-maintenance point of view: people WANT to be able to use syntactic sugar and still get good performance. Allowing for the insertion of optimization fences is good and important but it needs to be user-controllable behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Composite keys

for a query that only touches, say, c2. It's just extremely inefficient. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] does update of column with no relation imply a relation check of other column?

lock held by the other session would have blocked it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Composite keys

On Mon, Oct 31, 2011 at 1:52 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Oct 31, 2011 at 2:08 PM, Robert Haas robertmh...@gmail.com wrote: Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with kn. I don't think that's true.  I believe

Re: [PERFORM] Composite keys

On Mon, Oct 31, 2011 at 2:34 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Oct 31, 2011 at 3:24 PM, Robert Haas robertmh...@gmail.com wrote: Sure it does: rhaas=# create table baz (a bool, b int, c text, primary key (a, b)); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit

Re: [PERFORM] should i expected performance degradation over time

written in Perl, so I would think you could get it to work. But if not, you can always extract the big ol' query that it runs from the script and run it some other way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance

Re: [PERFORM] Shortcutting too-large offsets?

like it took 22.3 seconds to do the nested loop and then 22.4 seconds to do the nested loop plus the sort. So the sort itself only took 100 ms, which is hardly worth getting excited about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql

Re: [PERFORM] Performance problem with a table with 38928077 record

doing while this is running. I'd start the query up, let it run for 10 minutes or so, and then see whether the machine is CPU-bound or I/O-bound, and whether the amount of swap in use is growing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] Tsearch2 - bad performance with concatenated ts-vectors

that cross table boundaries tend to be expensive, because they have to be applied only after performing the join. You can't know for sure looking only at a row from one table whether or not it will be needed, so you have to join them all and then filter the results. -- Robert Haas EnterpriseDB

Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

On Oct 24, 2011, at 8:16 AM, Venkat Balaji venkat.bal...@verse.in wrote: Thanks Greg ! Sorry for delayed response. We are actually waiting to change the checkpoint_segments in our production systems (waiting for the downtime). That setting can be changed without downtime. ...Robert --

Re: [PERFORM] issue related to logging facility of postgres

it afterwards to split it up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Re: GROUP BY with reasonable timings in PLAN but unreasonable execution time

loop join estimator) does essentially that. I'm not sure I understand what you're getting at here, unless the idea is to make get_variable_numdistinct() somehow indicate to the caller whether it had to punt. That might be worth doing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] very large record sizes and ressource usage

. Large records just get broken up into small records, under the hood. At any rate, your email is a little vague about exactly what the problem is. If you provide some more detail you might get more help. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] hstore - Implementation and performance issues around its operators

On Tue, Jul 19, 2011 at 5:06 PM, Stefan Keller sfkel...@gmail.com wrote: 2011/7/19 Robert Haas robertmh...@gmail.com: Putting the elements in order wouldn't really help, would it?  I mean, you'd need some kind of an index inside the hstore... which there isn't. Sorry for my inprecise

Re: [PERFORM] Large rows number, and large objects

On Wed, Jul 20, 2011 at 11:57 AM, Jose Ildefonso Camargo Tolosa ildefonso.cama...@gmail.com wrote: On Tue, Jul 19, 2011 at 3:57 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 19, 2011 at 10:19 PM, Jose Ildefonso Camargo Tolosa ildefonso.cama...@gmail.com wrote: So, the question

Re: [PERFORM] hstore - Implementation and performance issues around its operators

this reversed from the convention mean concretely? That comment could be a little more clear, but I think what it's saying is that hstore's old @ is like the core geometic types old ~, and visca versa. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] Large rows number, and large objects

it would be a lot of work for the amount of benefit you'd get. There's an easy workaround: store the files in the filesystem, and a path to those files in the database. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance

Re: [PERFORM] bad plan: 8.4.8, hashagg, work_mem=1MB.

-- the stats for all tables are up to date (the tables never change after import).  statistics is set at 100 currently. The query and the full EXPLAIN output (attached as text files) would be a good place to start -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Poor performance when joining against inherited tables

On Wed, May 11, 2011 at 4:47 PM, Lucas Madar ma...@samsix.com wrote: On 05/11/2011 09:38 AM, Robert Haas wrote: However, if I disable seqscan (set enable_seqscan=false), I get the following plan:  QUERY PLAN  Hash Join  (cost=10001298843.53..290002337961.71 rows=8643757 width

Re: [PERFORM] change sample size for statistics

(n_distinct = ...); -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] poor performance when recreating constraints on large tables

for that is a suitable topic for a PhD thesis. :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] Understanding Hash Join performance

can I do to speed them up? 3. What can I do to enable Postgres to use a faster type of join? IME, hash joins usually are much faster than any other type. There's not enough information in your email to speculate as to what might be going wrong in your particular case, though. -- Robert Haas

Re: [PERFORM] Understanding Hash Join performance

. And shouldn't it? In a gross mode, when hash joins go to disk, they perform very poorly. Maybe the planner should take that into account. It does. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql

[PERFORM] picking a filesystem

? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hash Anti Join performance degradation

) (actual time=1.743..1.743 rows=20903 loops=113) Index Cond: (box_id = b.id) - Total runtime: 431520.186 ms + Total runtime: 6940.369 ms That's pretty odd. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] The shared buffers challenge

way to spread this sort of information. Hmm. That's rather unfortunate. +1 for revisiting that topic, if you have the energy for it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

On Tue, May 17, 2011 at 11:10 AM, m1...@hsr.ch wrote: For Hstore I'm using a GIST index. I would have thought that GIN would be a better choice for this workload. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing

Re: [PERFORM] Pushing LIMIT into sub-queries of a UNION ALL

by MergeAppend in 9.1. You might want to try 9.1beta1 and see if that works better for you. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] SORT performance - slow?

are right. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Performance degradation of inserts when database size grows

. What feature are you referring to here? Checkpoint spreading was added in 8.3, IIRC. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

to be an exercise in unjustified optimism of the first order. Sorry to sound grumpy and pessimistic, but I really think we're letting our enthusiasm get way, way ahead of the evidence. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

a tuple, you'd have to look at every column of the tuple and determine which histogram bucket it was in (or, presumably, which MCV it is, since those aren't included in working out the histogram buckets). That seems like it would slow down a sequential scan by at least 10x. -- Robert Haas

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

On Thu, May 19, 2011 at 2:39 PM, Jim Nasby j...@nasby.net wrote: On May 19, 2011, at 9:53 AM, Robert Haas wrote: On Wed, May 18, 2011 at 11:00 PM, Greg Smith g...@2ndquadrant.com wrote: Jim Nasby wrote: I think the challenge there would be how to define the scope of the hot-spot

Re: [PERFORM] Link error when use Pgtypes function in windows

, not just libpq. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move

good thought. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

is most actively modified, and the older data will be relatively more (though not completely) static, and less frequently accessed. Such examples are common in many real-world applications. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

seeks and scans in memory until proven otherwise. Well, anything's possible. But I wonder whether the effects you are describing might result from a reduction in the *number* of pages accessed rather than a change in the access pattern. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance = Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226

that read and write are treated same with Fio drives. I would think more like 0.1 than 1.0. Any suggestions on configuration changes to have read-only hot standby faster on READs. effective_io_concurrency? Adjust OS readahead? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] DBT-5 Postgres 9.0.3

://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

that a relation with only handful of pages isn't going to be cached. Even if it isn't, as soon as someone begins accessing it, it will be. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move

fairly sure it can happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

On Fri, May 13, 2011 at 3:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 26, 2011 at 9:04 PM, Merlin Moncure mmonc...@gmail.com wrote: The very first thing to check is effective_cache_size and to set it to a reasonable value. Actually

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

if the pages were consecutive on disk, there's no reason to suppose they would be so in memory, and we certainly wouldn't know one way or the other at planning time. But I agree we should add a cached_page_cost as part of all this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Query improvement

woulda believed, but... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Poor performance when joining against inherited tables

, is there any way I can make the query planner not balk over doing a seqscan on an empty table? Why would you care? A sequential scan of an empty table is very fast. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

towards smaller hashes due to hash probe cost increasing with hash size due to processor caching effects?  Its not quite O(n) due to caching effects. I don't think we account for that (and I'm not convinced we need to). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] DBT-5 Postgres 9.0.3

will be more of a support. What's going wrong for you? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Checkpoint execution overrun impact?

posted seem to reflect a system under very light load. Each checkpoint is writing no more than 4% of shared_buffers and the sync phases are generally completing in less than one second. I don't see why that would be causing stalls. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] help speeding up a query in postgres 8.4.5

[ woops, accidentally replied off-list, trying again ] On Tue, May 10, 2011 at 1:47 PM, Maria L. Wilson maria.l.wilso...@nasa.gov wrote: thanks for taking a look at this and it's never too late!! I've tried bumping up work_mem and did not see any improvements - All the indexes do exist

Re: [PERFORM] VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?

, you're going to have only 8GB of cache, instead of some much larger amount. More cache = better performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] index usage on queries on inherited tables

On Apr 27, 2011, at 11:11 PM, Joseph Shraibman j...@selectacast.net wrote: On 04/27/2011 04:32 PM, Robert Haas wrote: In the first case, PostgreSQL evidently thinks that using the indexes will be slower than just ignoring them. You could find out whether it's right by trying

Re: [PERFORM] Performance

On Apr 29, 2011, at 10:25 AM, James Mansion ja...@mansionfamily.plus.com wrote: Robert Haas wrote: The server can and does measure hit rates for the PG buffer pool, but to my knowledge there is no clear-cut way for PG to know whether read() is satisfied from the OS cache or a drive cache

Re: [PERFORM] Performance

On Apr 27, 2011, at 11:01 PM, Claudio Freire klaussfre...@gmail.com wrote: The patch may be simple, the testing not so much. I know that. What tools do we have to do that testing? There are lots, and all imply a lot of work. Is that work worth the trouble? Because if it is... why not work?

Re: [PERFORM] index usage on queries on inherited tables

you might need significantly lower values. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] %100 CPU on Windows Server 2003

-running queries. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] Slow deleting tables with foreign keys

are taking all the time, which might give you a clue where to go with it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

  1   2   3   4   5   6   7   >