Re: [PERFORM] postgres performance

y with dropping the index and letting them run in parallel. Each individual query would be slow but they should be smart enough to share each other's sequential scans - the disks would basically be looping through you data continuously. -- Richard Huxton Archonet Ltd -- 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 clustering for scale out works in PostgreSQL

es make up your "hits" and what you mean by 1000 then there are people on this list who can tell you what sort of setup you'll need. While you're away googling though, "replication" is indeed the term you want. In particular "hot standby" which lets you run

Re: [PERFORM] PostgreSQL 9.2.4 very slow on laptop with windows 8

nsely. Second step - have a quick look in your performance monitoring (you can get to it through -- Richard Huxton Archonet Ltd -- 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 properly index hstore tags column to faster search for keys

story. That assumes you are never interested in them of course. How exactly should I post the explain without the index? Do I have to drop all created indexes for the tags column? It takes some time to create them back. Not important - I was just curious. -- Richard Huxton Archonet Ltd -- S

Re: [PERFORM] How to properly index hstore tags column to faster search for keys

rows. There are 36351 of those, but presumably most of them are far away on the map. Could you post the explain without the index? I'm curious as to how slow it is just testing the tags after doing the geometry search. -- Richard Huxton Archonet Ltd -- Sent via pgsql-perform

Re: [PERFORM] How to properly index hstore tags column to faster search for keys

://explain.depesz.com/ if it's too long for the email. -- Richard Huxton Archonet Ltd -- 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 1 = 2 OR ..." makes planner choose a very inefficient plan

quot;, then the plan changes dramatically: What happens if you substitute: 1. 1=3 OR 2. false OR -- Richard Huxton Archonet Ltd -- 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] PsqL8.3

er, it might just be that I've misunderstood your description. More details please. -- Richard Huxton Archonet Ltd -- 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] Fighting the planner >:-(

ge it globally. If you've got the RAM try doubling it, then double it again. See what happens to your plan then. -- Richard Huxton Archonet Ltd -- 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] Triggers and transactions

a good idea anyway). Then the function can just check current_user and exit for the copy. -- Richard Huxton Archonet Ltd -- 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] Savepoints in transactions for speed?

. No reason why it shouldn't. https://metacpan.org/module/DBD::Pg#COPY-support -- Richard Huxton Archonet Ltd -- 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] transactions start time

tand: 1. All* commands run in a transaction 2. I think most of the work in getting a new snapshot etc gets pushed back until it's needed. So - the overall impact of issuing BEGIN should be close to zero. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-p

Re: [PERFORM] Create tables performance

ou do. Last time, I started a Java process I use to make some change on it, it created 170 new tables and it took one full minute. What are you using all these tables for? I'm assuming most of them have identical structure. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance ma

Re: [PERFORM] scale up (postgresql vs mssql)

the two operating-systems. It might be poor drivers on the CentOs system. Do you have two equivalent machines, or are you dual-booting? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Very long deletion time on a 200 GB database

tes can be very slow. -- Richard Huxton Archonet Ltd -- 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] unlogged tables

On 14/11/11 10:08, Sergey Konoplev wrote: On 14 November 2011 12:58, Richard Huxton wrote: Because they bypass the transaction-log (WAL), hence unlogged. There's no way to know whether there were partial updates applied when the system restarts. I probably did not understand the &quo

Re: [PERFORM] unlogged tables

o way to know whether there were partial updates applied when the system restarts. -- Richard Huxton Archonet Ltd -- 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 clause + function, execution order

ing reg_data_a08id_copy on registrations_data b (cost=0.00..1496.89 rows=24174 width=45) (actual time=0.008..70.408 rows=24174 loops=1)" "Total runtime: 372.765 ms" That certainly looks like it's been inlined. You are testing for "ITA10", "ITA15&qu

Re: [PERFORM] where clause + function, execution order

result not altering its execution.. Not true for SQL functions. They can be inlined, but I'm not sure if this one will be. What does EXPLAIN ANALYSE show for this query? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] How to boost performance of queries containing pattern matching characters

#x27; to confirm that fact. P.S. - I'd be inclined to just match the central domain parts, so for "us...@europe.megacorp.com" you would index "europe" and "megacorp" and only allow matching on the start of each string. Of course if your application spec says

Re: [PERFORM] How to boost performance of queries containing pattern matching characters

il) @@ to_tsquery('dom') AND to_tsvector('myftscfg',email) @@ to_tsquery('oma') AND to_tsvector('myftscfg',email) @@ to_tsquery('mai') AND ... Looks like you've almost re-invented the trigram module: http://www.postgresql.org/docs/9.0/static/pgt

Re: [PERFORM] How to boost performance of queries containing pattern matching characters

ince it doesn't know what you will search for). The index-size becomes unmanageable very quickly. That's why I asked what you really wanted to match. So, I'll ask again: do you really want to match all of those options? -- Richard Huxton Archonet Ltd -- Sent via pgsql-p

Re: [PERFORM] How to boost performance of queries containing pattern matching characters

us...@unrelated-domain.com us...@unrelated-domain.com.au us...@sub.domain.com.au us...@sub.unrelated-domain.com us...@sub.unrelated-domain.com.au us...@sub.unrelated-domain.completely-wrong.com Is that really what you are after? Or, did you just want to match: us...@domain.com us...@sub.domain.com

Re: [PERFORM] How to get FK to use new index without restarting the database

get postgres to use the new index for the FK cascade delete without bouncing the database. Well, an ongoing DELETE isn't going to see a new index. I'd have thought a new connection should though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-p

Re: [PERFORM] performance libpq vs JDBC

thing is a simple INSERT. Preparing saves planning-time on repeated SELECTs. It also provides some SQL injection safety since you provide parameters rather than building a SQL string. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or

Re: [PERFORM] performance libpq vs JDBC

sions - see exactly what is different. -- Richard Huxton Archonet Ltd -- 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 more tha one index per table

large "CURRENT MANUALS" link above all the versioned links. That should help substantially. -- Richard Huxton Archonet Ltd -- 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 Tuning Large PL/PGSQL Stored Procedure

ld make it pretty clear where the main costs are in getting your data back. -- Richard Huxton Archonet Ltd -- 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] Multiple data base on same server

have e.g. a "system" schema with tables "users", "activity_log" etc? There's no problem with 20-30 schemas per database. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Multiple data base on same server

s? Do you have 200-300+ disks to put these tablespaces on? If not, I'm not clear what you are trying to do. Why does each DB need 10 tablespaces? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes t

Re: [PERFORM] Extracting superlatives - SQL design philosophy

m guessing is true) then try an index on (a asc, b desc) and CLUSTER that index. Depending on the ratio of distinct a:b values that could be what you're after. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make change

Re: [PERFORM] Extracting superlatives - SQL design philosophy

ch has the highest value of Y, and so use a MAX-structured accumulation instead of a sort. Why is there only one row? For city temperatures, that seems unlikely. In the event of more than one row does your algorithm give repeatable results? -- Richard Huxton Archonet Ltd -- Sent

Re: [PERFORM] Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL

ch time. Running it once for all possible values and stashing the results in a temp table will probably be *much* faster. The planner can just scan the whole table once and build up its results as it goes. -- Richard Huxton Archonet Ltd -- 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] Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL

On 22/01/10 18:03, Tory M Blue wrote: On Fri, Jan 22, 2010 at 1:42 AM, Richard Huxton wrote: On 21/01/10 22:15, Tory M Blue wrote: 2. If it's mostly tagged=true you are interested in you can always use a partial index: CREATE INDEX ... (makeid) WHERE tagged This might be a win even i

Re: [PERFORM] Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL

isn't the query that is CPU bound for a long time. Unless your table is horribly bloated, there's no reason for that judging by this plan. -- Richard Huxton Archonet Ltd -- 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] Inserting 8MB bytea: just 25% of disk perf used?

ses aren't adjacent then that implies a seek of course. Worst case you get two or more processes each accessing different parts of the disk in an interleaved arrangement. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] Queryplan within FTS/GIN index -search.

Jeff Davis wrote: > On Fri, 2009-10-23 at 09:26 +0100, Richard Huxton wrote: >> That structure isn't exposed to the planner though, so it doesn't >> benefit from any re-ordering the planner would normally do for normal >> (exposed) AND/OR clauses. > > I don

Re: [PERFORM] Queryplan within FTS/GIN index -search.

indexscan (A & B & C => A if A is an uncommon word) and use the full query tree for the heap check. Now, what isn't clear to me on first glance is how to determine which phase of the bitmap scan we are in. HTH Just checking, because I don't think it's useful in this case. Bu

Re: [PERFORM] Queryplan within FTS/GIN index -search.

better solution, but #2 might well be simpler to implement as a work-around for now. -- Richard Huxton Archonet Ltd -- 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 problems with DISTINCT ON

ning a CLUSTER on the index you've created. That will lock the table while it re-orders the physical layout of the rows based on your index though, so it's no good if the table is updated much. Failing that, you could try issuing "set work_mem = ..." before the query with in

Re: [PERFORM] Possible causes of sometimes slow single-row UPDATE with trivial indexed condition?

n't have indexes on "status" or "updated_at" then you might want to read up on HOT and decrease your fill-factor on the table too. That's unrelated to this though. It looks like the problem is common enough that you could have a small script check pg_stat_activity on

Re: [PERFORM] Under the hood of views

le without a bit of analysis. -- Richard Huxton Archonet Ltd -- 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] Greenplum MapReduce

ays replace tabs with spaces. -- Richard Huxton Archonet Ltd -- 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] Greenplum MapReduce

time (commonly below the line you put something like "this is line 7 ^") The most common problem I get with YAML files though is when a tab is accidentally inserted instead of spaces at the start of a line. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance ma

Re: [PERFORM] Incr/Decr Integer

wondering if we should be doing something different. You can't get deadlocks with that - it only references one table. What is the purpose of this query - how are you using it? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] Very big insert/join performance problem (bacula)

d). maintenance_work_mem = 256MB max_fsm_pages = 1500 # There are quite big deletes with bacula ... effective_cache_size = 800MB See other emails on this one. default_statistics_target = 1000 Probably don't need this for all columns, but it won't cause problems with these queries.

Re: [PERFORM] Very big insert/join performance problem (bacula)

the way. That's what you need to set effective_cache_size to then. -- Richard Huxton Archonet Ltd -- 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] Data caching

up a bitmap of which disk-blocks contain (potential) matches. It then has to read the blocks (the heap scan above), confirm they match and then return the rows. If you look at the "actual time" above you can see about 90% of the slow query is spent doing this. -- Richard Huxton

Re: [PERFORM] slow DELETE on 12 M row table

.postgresql.org/docs/8.3/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY -- Richard Huxton Archonet Ltd -- 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] EXPLAIN understanding? (restarted from Censorship)

Please ignore - Matthew has discovered what was blocking this message. Use his thread instead. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[PERFORM] EXPLAIN understanding? (restarted from Censorship)

On Wed, 10 Jun 2009, Richard Huxton wrote: Send it to the list again, and cc: me directly if you like. If it doesn't show up in the next 20 minutes, I'll try sending it. Okay, here we go. I have (per Tom's advice) found some acknowledgement knobs on Majordomo. Here follows my or

Re: [PERFORM] Using index for bitwise operations?

there is one you always/mostly check against. The relational way to do this would be one or more property tables joined to your main table. If the majority of your properties are not set then this could be faster too. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance

Re: [PERFORM] Any better plan for this query?..

uery-time doesn't fall by much then it's the hash phase. The other thing to try is to alter the query to be a SELECT count(*) rather than returning rows - that will let you measure the time to transfer the result rows. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performa

Re: [PERFORM] Any better plan for this query?..

nd you are satisfied that it is the planned query time that is the dominant factor here, and not parsing time, connection time, data transport, disk bandwidth etc? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your su

Re: [PERFORM] Any better plan for this query?..

Dimitri wrote: Hi Chris, the only problem I see here is it's 2 times slower vs InnoDB How do you know? This isn't just based on the explain values reported, is it? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or

Re: [PERFORM] performance for high-volume log insertion

those too and perhaps stick them in their own schema (logs200901, logs200902 etc). -- Richard Huxton Archonet Ltd -- 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] Need help with one query

Index Cond: ((object_id)::text = 'artf414029'::text) > Total runtime: 6683.220 ms Very odd. It knows the table is large and that the seq-scan is going to be expensive. Try issuing "set enable_seqscan = off" and run the explain analyse again. That should s

Re: [PERFORM] LIMIT confuses the planner

olumn. ALTER TABLE login_attempt ALTER COLUMN username SET STATISTICS 100; ANALYZE login_attempt; You can try different values of statistics up to 1000, but there's no point in setting it too high. -- Richard Huxton Archonet Ltd -- 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] col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

use the index? They're not even equivalent queries: SELECT ... WHERE lower(col1) LIKE 'FOO%' SELECT ... WHERE col1 ILIKE 'FOO%' One is guaranteed to return no rows, the other not. -- Richard Huxton Archonet Ltd -- 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] Can't locate Test/Parser/Dbt2.pm in DBT2 tests

led DBT2 completely. Did I miss any steps? Do I need to install some > extra packages? If any then please let me know. You can always "perldoc perlrun" for more info (google it if you don't have docs installed locally). -- Richard Huxton Archonet Ltd -- 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] PG performance in high volume environment (many INSERTs and lots of aggregation reporting)

unning lots of small, simple queries it might make sense. If you want to aggregate data by varying criteria I don't think there is any sensible optimisation (other than pre-calculating summaries). -- Richard Huxton Archonet Ltd -- 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] Sort causes system to freeze

nnections I'd drop it down to (say) 4MB to make sure you don't swap on a regular basis (should probably be even lower to be truly safe). Then, for the odd case when you need a large value, issue a SET work_mem before the query. -- Richard Huxton Archonet Ltd -- 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] Increasing pattern index query speed

ook. > In testcase order_products contains product_id values in a very regular > order, maybe this affects the results. No idea how to use random() to > generate random > products for every order. Ideally you don't even want random products. You want a distribution of products that ma

Re: [PERFORM] Increasing pattern index query speed

tly longer. Try 100, 200, 500 and see if they work *for a range of queries* - there's no point in having it much higher than it needs to be. -- Richard Huxton Archonet Ltd -- 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] Increasing pattern index query speed

rformed vacuum and whole db reindex. > Tried several times to run two same pattern queries in quiet db. And the results were? > additonal condition One problem at a time. Let's get the pattern-matching speed problems on your live server sorted, then we can look at different queries.

Re: [PERFORM] Increasing pattern index query speed

an using orders_pkey on orders (cost=0.00..8.37 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=522) Index Cond: (orders.order_id = orders_products.order_id) Filter: (orders.order_date > '2006-01-01'::date) Total runtime: 8.335 ms (12 rows) -- Richard Huxton

Re: [PERFORM] Increasing pattern index query speed

ions Andrus? 1. Fix the vacuuming issue in your hash-join question. 2. Monitor the system to make sure you know if/when disk activity is high. 3. *Then* start to profile individual queries and look into their plans. Change the queries one at a time and monitor again. Otherwise, it's very di

Re: [PERFORM] Hash join on int takes 8..114 seconds

vacuum full pg_shdepend" and a "reindex pg_shdepend". If it is a million rows, you'll need to find out why. Do you have a lot of temporary tables that aren't being dropped or something similar? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance

Re: [PERFORM] Hash join on int takes 8..114 seconds

0.7 4.9 0:01.21 postmaster >1 root 16 0 1480 508 444 S 0.0 0.0 0:01.35 init Here you're stuck waiting for disks (91.0% wa). Check out vmstat and iostat to see what's happening. -- Richard Huxton Archonet Ltd -- 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 join on int takes 8..114 seconds

time: 29650.696 ms" > "Aggregate (cost=234278.53..234278.54 rows=1 width=0) (actual > time=11131.392..11131.396 rows=1 loops=1)" > "Total runtime: 11131.694 ms" So - what other activity is happening on this machine? Either other queries are taking up not

Re: [PERFORM] Difference in query plan

ore often? It might even be the particular order that rows are loaded - a btree can become "unbalanced" sometimes. -- Richard Huxton Archonet Ltd -- 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] Difference in query plan

nst table "mrp" and see if it's got a lot of dead rows. If it has, run VACUUM FULL and REINDEX against it and see if that solves your problem. I'm guessing you have / had a long-running transaction interfering with vacuum on this table, or perhaps a bulk update/delete? --

Re: [PERFORM] slow full table update

map Index Scan on pk_songs2 (cost=0.00..151.59 rows=8931 width=0) > (actual time=4.071..4.071 rows=9579 loops=1) > > Index Cond: ((sid > 2) AND (sid < 3)) > > Is there a way to run this query on sigle throughpass with no Recheck Cond? Only a sequential scan. -

Re: [PERFORM] Oddity with view

Seq Scan on loan_tasks_committed > (cost=0.00..929345.35 rows=26112135 width=162) (actual > time=0.014..22531.902 rows=26115689 loops=1) It's the width - the view is fetching all the rows. Is the "true as committed" bit confusing it? -- Richard Huxton Archonet Ltd -- 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 OR performance

599..19920.912 rows=2316503 loops=1)" > " Sort Key: production.company.run_id" > " Sort Method: external merge Disk: 104896kB" By constrast, this on-disk sort of 104MB is comparatively fast. > P.S. May be I've chosen wrong list and my Q

Re: [PERFORM] PostgreSQL OR performance

: "Nested Loop (cost=4588.13..960900482668.95 rows=1386158171 width=32)" You don't show "explain analyse" for this query, so there's no way of knowing how many rows get returned but presumably you're expecting around 88000. What does "explain analyse&quo

Re: [PERFORM] server space increasing very fast but transaction are very low

I'm guessing what you've got is a table that's not being vacuumed because you've had a transaction that's been open for weeks. -- Richard Huxton Archonet Ltd -- 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] 7.4 - basic tuning question

QUERY PLAN > > --- > Aggregate (cost=5101.43..5101.43 rows=1 width=0) >-> Index Scan using log_event on log (cost=0.00..5098.15 rows=1310 > width=0) > Index Cond: (event = &#x

Re: [PERFORM] dedicated server & postgresql 8.1 conf tunning

Tommy Gildseth wrote: > Richard Huxton wrote: >> [EMAIL PROTECTED] wrote: >>> Thanks, >>> >>> Unfornatly, i can't update pgsql to 8.3 since it's not in debian stable. >> >> That's why backports.org was invented :-) >> Or does ca

Re: [PERFORM] dedicated server & postgresql 8.1 conf tunning

compute the go shmmax for my server ? I'm not seeing anything terribly wrong there. Are you hitting a limit with shmall? Oh - and I'm not sure there's much point in having more shared-buffers than you have data. Try much larger work_mem first, I think that's the biggest g

Re: [PERFORM] bizarre query performance question

ul of rows with scenario_id=0 then I'd expect it to switch to a "standard" index scan. If your work_mem is small try something like: set work_mem = '50MB'; before running the query - maybe even larger. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance m

Re: [PERFORM] dedicated server & postgresql 8.1 conf tunning

tmonth = ... and tday = ... AND > audiotel IN ( '...', '...' ); > or select ... SUM( CASE WHEN condition THEN value ELSE 0) ... FROM > cirpacks.tickets WHERE '2007-01-01' <= date AND date <= '2008-08-31' AND > audiotel IN ( '...'

Re: [PERFORM] Restoration of datas

dexes etc. It will take a long time to do a full restore though - you've got 64GB of data and slow disks. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mail

Re: [PERFORM] Plz Heeeelp! performance settings

information > for other insert to make links between data. Why does that stop you putting all 14000 calls in one transaction? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.pos

Re: [PERFORM] Plz Heeeelp! performance settings

ltiplying this per 1, it is too long. So - are you calling this function 14000 times to inject your data? You're doing this in one transaction, yes? -- Richard Huxton Archonet Ltd -- 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] Query Plan choice with timestamps

Giorgio Valoti wrote: On 07/ago/08, at 10:35, Richard Huxton wrote: Giorgio Valoti wrote: Hi, I have a timestamptz field that I want to use with a query, but I don’t need the full timestamp resolution, so I’ve created a day_trunc(timestamptz) immutable function which I’ll use with the

Re: [PERFORM] Plz Heeeelp! performance settings

y for buffers/cache)? total used free sharedbuffers cached Mem: 7984 7828156 0 38 7349 -/+ buffers/cache:440 7544 Swap: 509 1508 Not far off - free is showing 7349MB cached.

Re: [PERFORM] Query Plan choice with timestamps

box (day_trunc(ts)); However, the query plan doesn’t use the index: Does it use it ever? e.g. with SELECT * FROM blackbox WHERE day_trunk(ts) = '...' -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Plz Heeeelp! performance settings

k you probably need to make your autovacuum more aggressive, but that's something you'll be able to tell by monitoring your database. It's quite likely that Merlin's right, and you need better hardware to cope with the number of updates you're making - that's som

Re: [PERFORM] Unexpectedly Long DELETE Wait

ively, if you increased your work_mem that might help. Try SET work_mem='64MB' (or even higher) before running the explain and see if it tries a materialize. For situations like this where you're doing big one-off queries you can afford to increase resource limits. -- Richard Hu

Re: [PERFORM] Nls sorting in Postgresql-8.3.3

one know. What is "nls sort"? What do you expect --enable-nls to do? It looks like it's for multi-language message display rather than sorting. The locale options are already built-in. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pg

Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

stall multiple versions of PostgreSQL on the same machine. Might be worth bypassing it and calling it directly. -- Richard Huxton Archonet Ltd -- 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 fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

Miernik wrote: Richard Huxton <[EMAIL PROTECTED]> wrote: I just installed pgpool2 and whoaaa! Everything its like about 3 times faster! My application are bash scripts using psql -c "UPDATE ...". Probably spending most of their time setting up a new connection, then cleari

Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

f Debian location)? Not well known enough on the Debian side of the fence? It's simple enough to install from source though. Takes about one minute. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subs

Re: [PERFORM] Database size Vs performance degradation

Dave North wrote: -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Well, that's pretty much the definition of bloat. Are you sure you're vacuuming enough? DN: Well, the auto-vac is kicking off pretty darn frequently...around once every 2 minutes. However

Re: [PERFORM] Database size Vs performance degradation

going to take some time :) I think you'll like some of the improvements, but it's probably more important to get 8.1.13 installed soon-ish. -- Richard Huxton Archonet Ltd -- 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 fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

and set them to allow only one connection in the pool. I know that pgbouncer offers per-transaction connection sharing which will make this more practical. Even so, it will help if your application can co-operate by closing the connection as soon as possible. -- Richard Huxton Archone

Re: [PERFORM] Less rows -> better performance?

ce. 5. Throw in a spare machine as an app server for the first week of term. Presumably your load is 100 times average at this time. -- Richard Huxton Archonet Ltd -- 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] Less rows -> better performance?

in memory. What in particular is slow? -- Richard Huxton Archonet Ltd -- 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] Does max size of varchar influence index size

longer fixed at 32 bits but can adjust itself automatically. Apart from the overheads, you need the space to store the text in each string, not the maximum possible. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

  1   2   3   4   5   6   >