Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

where the you want to write several blocks at once and then force them all out together. And 64kB for a ring buffer just seems awfully small. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

implementation we could use... -- 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] Running PostgreSQL as fast as possible no matter the consequences

chose to create as unlogged will be truncated, and the rest of your data, including the system catalogs, will still be intact. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

On Mon, Nov 15, 2010 at 2:27 PM, Andy Colson a...@squeakycode.net wrote: On 11/15/2010 9:06 AM, Robert Haas wrote: In 9.1, I'm hopeful that we'll have unlogged tables, which will even better than turning these parameters off, and for which I just posted a patch to -hackers.  Instead

Re: [PERFORM] Difference between explain analyze and real execution time

on \timing, and then run EXPLAIN ANALYZE from within an interactive session? That's usually a better way to test, as it avoids counting the session-startup overhead. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing

Re: [PERFORM] Why dose the planner select one bad scan plan.

, and uses that to estimate the probability that the data are already in the filesystem cache. No, it does not do that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] anti-join chosen even when slower than old plan

information source for the planner This could possibly affect parameters of your formula on the fly. Yeah, I've thought about this, but it's not exactly clear what would be most useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql

Re: [PERFORM] temporary tables, indexes, and query plans

which CREATE INDEX is running, like a cursor? -- 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] temporary tables, indexes, and query plans

On Sat, Nov 13, 2010 at 7:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: OK, this is an artifact of the HOT update optimization.  Before creating the index, you did updates on the table

Re: [PERFORM] anti-join chosen even when slower than old plan

be done at all. -- 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] anti-join chosen even when slower than old plan

the whole plan. -- 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] questions regarding shared_buffers behavior

. -- 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] anti-join chosen even when slower than old plan

have to configure. But for people who are getting bitten by inaccurate n_distinct estimates, it will be very nice to have that as an escape hatch. I see no harm, and much value, in providing similar escape hatches elsewhere. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] anti-join chosen even when slower than old plan

On Thu, Nov 11, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Let's back up a moment and talk about what the overall goal is, here. Ideally, we would like PostgreSQL to have excellent performance at all times, under all circumstances

Re: [PERFORM] anti-join chosen even when slower than old plan

scripts that do the full-table-scan queries.  Unfortunately we don't have an equivalent of per-session SET (much less SET LOCAL) for per-relation attributes. Not sure if we want to go there. I doubt it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] anti-join chosen even when slower than old plan

On Thu, Nov 11, 2010 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yeah.  For Kevin's case, it seems like we want the caching percentage to vary not so much based on which table we're hitting at the moment but on how much of it we're actually reading

Re: [PERFORM] anti-join chosen even when slower than old plan

, effective_cache_size. Unfortunately, to know how much data we're going to grovel through, we need to know the plan; and to decide on the right plan, we need to know how much data we're going to grovel through. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [PERFORM] anti-join chosen even when slower than old plan

On Wed, Nov 10, 2010 at 6:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Robert Haas robertmh...@gmail.com wrote: Unfortunately, to know how much data we're going to grovel through, we need to know the plan; and to decide on the right plan, we

Re: [PERFORM] Regression: 8.3 2 seconds - 8.4 100+ seconds

a bit more if 33 is really 33.4999 rounded down, and 2 is really 2.4 rounded down). But the actual estimate is 5 orders of magnitude larger. How is that possible? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing

Re: [PERFORM] BBU Cache vs. spindles

to a number you can count on one hand (maybe one finger) would probably be bad. Maybe on 64-bit it would be OK but it seems like an awful lot of complexity for at most a minor savings (and a pretty bad anti-savings if point #1 kicks in). Anyway this is all totally off-topic... -- Robert Haas

Re: [PERFORM] temporary tables, indexes, and query plans

can reach that table's content inside that transaction, if you are creating, populating, and querying it all within that single transaction. Actually I don't think that's a problem, at least for a manual ANALYZE. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Slow Query- Simple taking

are taking 402 ms. The sort is then taking another 800+ ms for a total of 1325 ms. Any additional time is spent returning rows to the client. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Slow Query- Simple taking

On Thu, Oct 28, 2010 at 10:39 AM, Robert Haas robertmh...@gmail.com wrote: Can someone tell me why after it runs the index scan it hen runs a bitmap heap scan?  It should not take this long to run should it?  If I limit the results it comes back in 300ms. It doesn't.  The EXPLAIN output shows

Re: [PERFORM] Slow Query- Simple taking

called index-only scans that we don't have yet in PG, which would help cases like this a great deal. But I don't see how MySQL could send back 116,000 rows to the client in milliseconds, or sort them that quickly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [PERFORM] Slow Query- Simple taking

:)  9.0's features are already frozen. Well, with all this global warming around us, index scans may still thaw in time to make it into 9.0.2 I fear this is not going to happen for 9.1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] Query much faster with enable_seqscan=0

, but don't include it unless you need 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/pgsql-performance

Re: [PERFORM] how to get the total number of records in report

query again.  This can be win or loss depending on the query. What about select crm.*, sum(1) over () as crm_count from crm limit 10; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] BBU Cache vs. spindles

On Wed, Oct 27, 2010 at 12:41 AM, Rob Wultsch wult...@gmail.com wrote: On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch wult...@gmail.com wrote: The double write buffer is one of the few areas where InnoDB does more IO

Re: [PERFORM] Re: Postgres insert performance and storage requirement compared to Oracle

of any transaction which touches only temp tables. -- 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] Auto ANALYZE criteria

to have... -- 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] BBU Cache vs. spindles

, we replay the full page image. The good thing about this is that it would reduce WAL volume; the bad thing about it is that it would probably mean doing two fsyncs where we only now do one. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [PERFORM] BBU Cache vs. spindles

files would be bigger). PG on the other hand *is* pushing its logs over the wire... So how is InnoDB doing replication? Is there a second log just for that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list

Re: [PERFORM] Select count(*), the sequel

number of datatypes that require 4 or 8 byte alignment. How much is that hurting us? - Compression. Maybe Oracle's algorithm does better than PGLZ. If we can quantify where we're losing vs. Oracle - or any other competitor - that might give us some idea where to start looking. -- Robert Haas

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

on little (cost=0.00..1.10 rows=10 width=4) (5 rows) This doesn't appear to make a lot of sense, 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

Re: [PERFORM] odd postgresql performance (excessive lseek)

at the cost of putting in some other communication mechanism instead. I don't get it. Why would be doing that in a tight loop within a single backend? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Slow count(*) again...

On Wed, Oct 13, 2010 at 1:59 PM, Jesper Krogh jes...@krogh.cc wrote: On 2010-10-13 15:28, Robert Haas wrote: On Wed, Oct 13, 2010 at 6:16 AM, Neil Whelchelneil.whelc...@gmail.com  wrote: I might go as far as to rattle the cage of the developers to see if it makes any sense to add some

Re: [PERFORM] Slow count(*) again...

it's far more expensive than the value of the service they're providing can justify. (Let's not even talk about a $100,000 project.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] How does PG know if data is in memory?

small cache with large I/O buffers.  This avoided blowing out the default cache space for situations which almost always required disk I/O anyway. I think, but am not quite sure, that my answer to point #1 is also relevant here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Slow count(*) again...

. -- 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] Slow count(*) again...

and the updated table was not, but how and why would that 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

Re: [PERFORM] Slow count(*) again...

on a column so that the back end could shadow or store a column in a column oriented table so aggregate functions could work on them with good efficiency, or is that an INDEX? I'd love to work on that, but without funding it's tough to find the time. It's a big project. -- Robert Haas EnterpriseDB

Re: [PERFORM] How does PG know if data is in memory?

to a DB2-like structure at some point.  Yes? We already have tablespaces, and our data already is accessed through the buffer pool. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Runtime dependency from size of a bytea field

a native libpq connection, but I don't know that for a fact, not being an ODBC user. -- 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] gist indexes for distance calculations

this what the knngist patches are for? https://commitfest.postgresql.org/action/patch_view?id=350 http://www.sai.msu.su/~megera/wiki/knngist Those are for when you want to order by distance; the OP is trying to *filter* by distance, which is different. -- Robert Haas EnterpriseDB: http

Re: [PERFORM] How does PG know if data is in memory?

the system bogged down to a standstill until then. But changing the cost model isn't going to help them either. -- 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] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On Wed, Oct 6, 2010 at 10:07 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: It's good to be you. They're HP BL465 G7's w/ 2x 12-core AMD processors and 48G of RAM. Unfortunately, they currently only have local storage, but it seems unlikely

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On Thu, Oct 7, 2010 at 1:21 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: perhaps it would be possible by, say, increasing the number of lock partitions by 8x.  It would be nice to segregate these issues though, because using pread/pwrite

Re: [PERFORM] Odd behaviour with redundant CREATE statement

CONCURRENTLY might be an option, or you could write and call a PL/pgsql function (or, in 9.0, use a DO block) to test for the existence of the index before trying create it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

, there are lock free algorithms using CAS, no? OTOH, pread() / pwrite() don't have to do that. Hey, I didn't know about those. That sounds like it might be worth investigating, though I confess I lack a 48-core machine on which to measure the alleged benefit. -- Robert Haas EnterpriseDB: http

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On Wed, Oct 6, 2010 at 9:30 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: Hey, I didn't know about those.  That sounds like it might be worth investigating, though I confess I lack a 48-core machine on which to measure the alleged benefit. I've got

Re: [PERFORM] Query much faster with enable_seqscan=0

to be exactly perfect for both cases. I might try something more like 0.2 / 0.1. If you really need the query to be fast, though, you might need to do more than jigger the page costs. Did you try Tom's suggested rewrite? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres

Re: [PERFORM] how to enforce index sub-select over filter+seqscan

mostly cached in memory? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] turn off caching for performance test

are almost certainly too high. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Is disableing nested_loops a bad idea ?

of options... Incidentally, it's Haas, rather than Hass. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Using Between

you're unhappy with how the query is running or just curious about why the index isn't being used. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Using Between

the system. If you can avoid the need to write lower(x) = lower(y) and just write x = y you may get better plans. I'm not sure that's the case in this particular example but it's something to think about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent

Re: [PERFORM] Useless sort by

it to. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Useless sort by

struggling to think of an adequate response to this. I think I'm going to go with: huh? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] GPU Accelerated Sorting

, I think. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Using Between

in postgresql.conf? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] slow DDL creation

Is the referenced column indexed? no, but its for a new partition so there's no data as of yet in the partition What exactly does stinks mean? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

. Either VACUUM or CLUSTER will recover *dead* tuples. What you can't recover are tuples that are still visible to some running transaction. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Two fast searches turn slow when used with OR clause

be interesting if the other half of the WHERE clause turns out to hold. In the second case, you can throw away 297,305 of those 297,306 rows before doing anything else, because there's no possibility that they can ever be interesting. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [PERFORM] Questions on query planner, join types, and work_mem

, and unless you have tables or indices that are larger than RAM, the only mistake you can make is setting it too low. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Questions on query planner, join types, and work_mem

On Tue, Aug 3, 2010 at 3:03 AM, Hannu Krosing ha...@2ndquadrant.com wrote: In case of fully cached database it is closer to 1. In the case of a fully cached database I believe the correct answer begins with a decimal point. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Strange explain on partitioned tables

for the sake of organization in this situation...) Yeah, I wouldn't expect planning time to be affected by whether a table has parents; only whether it has children. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list

Re: [PERFORM] Questions on query planner, join types, and work_mem

, releases prior to 9.0 don't display that information. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Explains of queries to partitioned tables

/wiki/SlowQueryQuestions -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Pooling in Core WAS: Need help in performance tuning.

.  While not ideal, this would be good enough for 90% of users. However, if we don't support that, we can't do any sort of pooling-ish thing without the ability to pass file descriptors between processes; and Tom seems fairly convinced there's no portable way to do that. -- Robert Haas

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Wed, Jul 28, 2010 at 4:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: However, if we don't support that, we can't do any sort of pooling-ish thing without the ability to pass file descriptors between processes; and Tom seems fairly convinced there's

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Sat, Jul 24, 2010 at 2:23 AM, Craig Ringer cr...@postnewspapers.com.au wrote: On 24/07/10 01:28, Robert Haas wrote: Well, if we could change the backends so that they could fully reinitialize themselves (disconnect from a database to which they are bound, etc.), I don't see why we couldn't

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Tue, Jul 27, 2010 at 4:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jul 22, 2010 at 5:29 PM, Andres Freund and...@anarazel.de wrote: The problem is harder for us because a backend can't switch identities once it's been assigned to a database

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Tue, Jul 27, 2010 at 9:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jul 27, 2010 at 4:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Flushing them all is not zero-cost; it's not too hard to believe that it could actually be slower than forking

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Fri, Jul 23, 2010 at 11:58 AM, Hannu Krosing ha...@krosing.net wrote: On Thu, 2010-07-22 at 20:57 -0400, Robert Haas wrote: On Thu, Jul 22, 2010 at 3:15 PM, Hannu Krosing ha...@krosing.net wrote: On Thu, 2010-07-22 at 14:36 -0400, Robert Haas wrote: On Mon, Jul 12, 2010 at 6:58 AM, Craig

Re: [PERFORM] Slow query with planner row strange estimation

with query planner problems. Disabling nestloops altogether, even for one particular query, is often going to be a sledgehammer where you need a scalpel. But then again, a sledgehammer is better than no hammer. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

a backend can't switch identities once it's been assigned to a database. I haven't heard an adequate explanation of why that couldn't be changed, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Pooling in Core WAS: Need help in performance tuning.

On Thu, Jul 22, 2010 at 3:15 PM, Hannu Krosing ha...@krosing.net wrote: On Thu, 2010-07-22 at 14:36 -0400, Robert Haas wrote: On Mon, Jul 12, 2010 at 6:58 AM, Craig Ringer cr...@postnewspapers.com.au wrote: So rather than asking should core have a connection pool perhaps what's needed

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

is that you can keep some of them around, in which case, great. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Slow query with planner row strange estimation

with this ugly balanced data, I'm open to it ! I hope that in real production, data will never be loaded this way. If this appened we will maybe set enable_nestloop to off, but I don't think it's a good solution, other query have a chance to get slower. Yeah, that usually works out poorly. -- Robert

Re: [PERFORM] Need help in performance tuning.

there are good ones available, but the fact that they're absolutely necessary for good performance in some environments is not a feature. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Slow query with planner row strange estimation

. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

should be recyclable. Unless I'm confused, which apparently I am. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] big data - slow select (speech search)

! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Highly Efficient Custom Sorting

in the FuncCallContext (funcctx-user_fctx); and then on subsequent calls just return one row per call. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Two equivalent WITH RECURSIVE queries, one of them slow.

, but with a slightly different recursive query that wouldn't be true, so you'd need to make the code pretty smart to work this one out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Question about partitioned query behavior

a developer there are a number of companies that will be happy to work with you. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] big data - slow select (speech search)

of result on PostgreSQL. Using the full-text search stuff, or a gin index of some kind, might get you closer, but it's hard to beat a special-purpose engine that implements exactly the right algorithm for your use case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres

Re: [PERFORM] Performance issues with postgresql-8.4.0: Query gets stuck sometimes

sort of like a checkpoint spike. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Low perfomance SUM and Group by large databse

- then it could consider index-scanning for the matching rows and sorting them afterwards. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] No hash join across partitioned tables?

-committers/2010-06/msg00144.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] PostgreSQL as a local in-memory cache

On Mon, Jun 28, 2010 at 5:57 PM, Bruce Momjian br...@momjian.us wrote: The patch also documents that synchronous_commit = false has potential committed transaction loss from a database crash (as well as an OS crash). Is this actually true? -- Robert Haas EnterpriseDB: http

Re: [PERFORM] PostgreSQL as a local in-memory cache

On Tue, Jun 29, 2010 at 9:32 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Jun 28, 2010 at 5:57 PM, Bruce Momjian br...@momjian.us wrote: The patch also documents that synchronous_commit = false has potential committed transaction loss from a database crash (as well

Re: [PERFORM] WAL+Os on a single disk

On Thu, Jun 24, 2010 at 10:55 AM, Anj Adu fotogra...@gmail.com wrote: What would you recommend to do a quick test for this? (i.e WAL on internal disk vs WALon the 12 disk raid array )? Maybe just pgbench? http://archives.postgresql.org/pgsql-performance/2010-06/msg00223.php -- Robert Haas

Re: [PERFORM] PostgreSQL as a local in-memory cache

table is of course a must ; - for the sessions table you can drop the D You're trying to solve a different use-case than the one I am. Your use-case will be solved by global temporary tables.  I suggest that you give Robert Haas some help feedback on that. My use case is people using PostgreSQL

Re: [PERFORM] requested shared memory size overflows size_t

a WHERE relkind = 'r' to the above query anyway. Yeah - also, it would probably be good to call pg_relation_size on pg_class.oid rather than pg_class.relname, to avoid any chance of confusion over which objects are in which schema. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [PERFORM] Aggressive autovacuuming ?

and above need that anymore.  I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. -- Robert Haas EnterpriseDB: http

Re: [PERFORM] Aggressive autovacuuming ?

On Wed, Jun 23, 2010 at 2:20 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I

Re: [PERFORM] PostgreSQL as a local in-memory cache

-safe wal_level that eliminates WAL activity            * http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php I don't think we need a system-wide setting for that. I believe that the unlogged tables I'm working on will handle that case. -- Robert Haas EnterpriseDB: http

Re: [PERFORM] PostgreSQL as a local in-memory cache

will be truncated. That should give you the ability to do this (by making all your tables unlogged). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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   >