Re: [PERFORM] Slow query with 3 table joins

This looks like the same optimizer problem that occasionally plagues our customers. Whenever the estimated rows of a join==1, but the actual rows is higher, the optimizer may choose very poor plans. I made some attempts to fix. The very simple fix is to never estimate 1 for a join result. Even u

[PERFORM] Performance difference between Slon master and slave

not disable hash aggregation on the slave as this might have other consequences so this raises a number of questions. Firstly Is there anything that I can do to stop this feature? Why is the slave behaving differently to the master? Thanks in advance for any help. Cheers Matthew explain with

[PERFORM] Query optimizer plans with very small selectivity estimates

This related to a post in the general bugs forum, but I found this forum, and this seems more appropriate. This is my second attempt to post, I believe the first attempt last week did not work, apologies if I'm duplicating. http://comments.gmane.org/gmane.comp.db.postgresql.bugs/39011 I made ha

Re: [PERFORM] Stalls on PGSemaphoreLock

to close the loop on this and to thank the community again for their support. Best, Matt From: Pavy Philippe [philippe.p...@worldline.com] Sent: Tuesday, March 25, 2014 4:10 PM To: Matthew Spilich; pgsql-performance@postgresql.org Subject: RE: [PERFORM

[PERFORM] semaphore waits and performance stall

Hi everyone! I've been working on a puzzling issue for a few days am am hoping that someone has seen something similar or can help. There have been some odd behaviors on one of my production facing postgres servers. version info from postgres: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, c

Re: [PERFORM] Stalls on PGSemaphoreLock

...@worldline.com] Sent: Tuesday, March 25, 2014 1:45 PM To: Ray Stell; Matthew Spilich Cc: pgsql-performance@postgresql.org Subject: RE : [PERFORM] Stalls on PGSemaphoreLock Hello Recently I have a similar problem. The first symptom was a freeze of the connection and 100% of CPU SYS dur

[PERFORM] Stalls on PGSemaphoreLock

Hi everyone! I've been working on a puzzling issue for a few days am am hoping that someone has seen something similar or can help. There have been some odd behaviors on one of my production facing postgres servers. version info from postgres: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, c

Re: [PERFORM] DELETE vs TRUNCATE explanation

Tom Lane wrote: > (3) The performance of the truncation itself should not be viewed in > isolation; subsequent behavior also needs to be considered. An example > of possible degradation is that index bloat would no longer be > guaranteed to be cleaned up over a series of repeated truncations. > (Y

Re: [PERFORM] Sorted group by

-> GroupAggregate (cost=50965693.08..54272883.48 rows=29397248 width=17) -> Sort (cost=50965693.08..51700624.28 rows=293972480 width=17) Sort Key: public.tracker.objectid, public.tracker.fieldname -> Seq Scan on tr

Re: [PERFORM] Sorted group by

mber) FROM table I may be mistaken, but as I understand it, a windowing function doesn't reduce the number of rows in the results? Matthew -- Don't worry about people stealing your ideas. If your ideas are any good, you'll have to ram them down people's throats. -- H

[PERFORM] Sorted group by

that this is something that can be done in the new Postgres 9, with a sorted group by - something like this: SELECT group, LAST(value, ORDER BY number) FROM table GROUP BY group Is this something that is already built in, or would I have to write my own LAST aggregate function? Matthew -

Re: [PERFORM] Advice configuring ServeRAID 8k for performance

RAID6 set, then there is no data loss. Matthew -- And the lexer will say "Oh look, there's a null string. Oooh, there's another. And another.", and will fall over spectacularly when it realises there are actually rather a lot. - Computer Science Lecturer (edited

Re: [PERFORM] Testing Sandforce SSD

On Mon, 26 Jul 2010, Greg Smith wrote: Matthew Wakeling wrote: Does your latency graph really have milliseconds as the y axis? If so, this device is really slow - some requests have a latency of more than a second! Have you tried that yourself? If you generate one of those with standard

Re: [PERFORM] Testing Sandforce SSD

On Sun, 25 Jul 2010, Yeb Havinga wrote: Graph of TPS at http://tinypic.com/r/b96aup/3 and latency at http://tinypic.com/r/x5e846/3 Does your latency graph really have milliseconds as the y axis? If so, this device is really slow - some requests have a latency of more than a second! Matthew

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

le load of non-portable stuff? It seems to work on a whole load of platforms. Matthew -- I would like to think that in this day and age people would know better than to open executables in an e-mail. I'd also like to be able to flap my arms and fly to the moon.

Re: [PERFORM] Need help in performance tuning.

t would be held for such short periods that I suspect that collisions would be relatively infrequent compared to some of the other locks we use. As noted in the email, it may actually normally be an "increment and test" within an existing locked block. Fair enough. It may be much less

Re: [PERFORM] Need help in performance tuning.

d of contention. Matthew -- Unfortunately, university regulations probably prohibit me from eating small children in front of the lecture class. -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] Two different execution plan for the same request

On Wed, 7 Jul 2010, JOUANIN Nicolas (44) wrote: It seems to work fine (same execution plan and less duration) after :  - setting default_statistics_target to 100  - full vacuum with analyze Don't do VACUUM FULL. Matthew -- I suppose some of you have done a Continuous Maths course

Re: [PERFORM] how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer

st its not very popular. It's not OpenVz that is wierd, but virtualisation in general. If you are running in a virtual machine, then all sorts of things will not run as well as expected. Matthew -- Contrary to popular belief, Unix is user friendly. It just happens to be very selectiv

Re: [PERFORM] Highly Efficient Custom Sorting

the level of comparators. Matthew -- For those of you who are into writing programs that are as obscure and complicated as possible, there are opportunities for... real fun here -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-perfo

Re: [PERFORM] ideal storage configuration

your read speeds for your reporting queries, then increase the amount of RAM, as Kevin said, and see if you can fit the active portion of the database into RAM. Matthew -- Nog: Look! They've made me into an ensign! O'Brien: I didn't know things were going so badly.

Re: [PERFORM] Write performance

7;s quite right, and typical performance figures for a drive like that. Matthew -- Don't criticise a man until you have walked a mile in his shoes; and if you do at least he will be a mile behind you and bare footed. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

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

. Matthew -- I don't want the truth. I want something I can tell parliament! -- Rt. Hon. Jim Hacker MP -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [PERFORM] raid10 write performance

x27;s why you need a little bit of non-volatile storage to mediate that to properly support barriers. Of course, from a performance point of view, yes, you need some NVRAM on any kind of spinning storage to maintain high commit rates. Matthew -- I wouldn't be so paranoid if you weren&#x

Re: [PERFORM] B-Heaps

On Fri, 18 Jun 2010, Robert Haas wrote: On Tue, Jun 15, 2010 at 8:23 AM, Matthew Wakeling wrote: Absolutely, and I said in http://archives.postgresql.org/pgsql-performance/2010-03/msg00272.php but applied to the Postgres B-tree indexes instead of heaps. This is an interesting idea. I would

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

data changes, but I think in most circumstances that is not going to be a large burden on performance. Matthew -- Picard: I was just paid a visit from Q. Riker: Q! Any idea what he's up to? Picard: No. He said he wanted to be "nice" to me. Riker: I'll alert the crew. -- Se

Re: [PERFORM] Parallel queries for a web-application |performance testing

se queries can be fired at Postgres, and consume quite a lot of CPU. Use a proper benchmarking tool, possibly on a different server. Also, you should be using a different username to "postgres" - that one is kind of reserved for superuser operations. Matthew -- People who love sausa

Re: [PERFORM] B-Heaps

tgres has not implemented this in any of its indexing systems. Matthew -- An ant doesn't have a lot of processing power available to it. I'm not trying to be speciesist - I wouldn't want to detract you from such a wonderful creature, but, well, there isn

Re: [PERFORM] slow query performance

40 and 100 times faster than random pages, depending on the drive. However, caches tend to favour index scans much more than sequential scans, so using a value between 40 and 100 would discourage Postgres from using indexes when they are really the most appropriate option. Matthew -- A. Top

Re: [PERFORM] Weird XFS WAL problem

controller had some logic that said "it's OK to not flush the cache when that call comes in if my battery is working fine", that would make this whole problem go away. The only place this can be properly sorted is the RAID controller. Anywhere else would be crazy. Matthew -- &q

Re: [PERFORM] slow query

d, thedate) on each table. random_page_cost=1 I agree with Tomas that this is rarely a useful setting. Matthew -- You can configure Windows, but don't ask me how. -- Bill Gates -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your sub

Re: [PERFORM] Weird XFS WAL problem

o, then it should be fast regardless of barriers (although barriers may make a small difference). If it does not, then it is likely that the fast speed you are seeing with barriers off is unsafe. There should be no "just missed the sector going past for write" problem ever with a b

Re: [PERFORM] SELECT ignoring index even though ORDER BY and LIMIT present

every single row in the table to return the matching rows, so a sequential scan, filter, and sort would be much faster. Of course, if you had an index capable of answering the WHERE clause, that would be even better for that case. Matthew -- Don't criticise a man until you have walked a m

Re: [PERFORM] Overusing 1 CPU

mmand line. Matthew -- Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. - Kernighan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or

Re: [PERFORM] PostgreSQL Function Language Performance: C vs PL/PGSQL

On Tue, 1 Jun 2010, Stephen Frost wrote: * Matthew Wakeling (matt...@flymine.org) wrote: The major case I found when writing pl/pgsql was when trying to build arrays row by row. AFAIK when I tried it, adding a row to an array caused the whole array to be copied, which put a bit of a damper on

Re: [PERFORM] PostgreSQL Function Language Performance: C vs PL/PGSQL

it, adding a row to an array caused the whole array to be copied, which put a bit of a damper on performance. Matthew -- "The problem with defending the purity of the English language is that English is about as pure as a cribhouse whore. We don't just borrow words; on occasion, Engli

Re: [PERFORM] Optimize date query for large child tables: GiST or GIN?

CLUSTERing the tables on the index. Matthew -- And the lexer will say "Oh look, there's a null string. Oooh, there's another. And another.", and will fall over spectacularly when it realises there are actually rather a lot. - Computer Science Lecturer (edited) -- Sent v

Re: [PERFORM] prepared query performs much worse than regular query

values were available. The whole idea is to avoid the planning cost each time the query is executed, but if your data is unusual it can result in worse plans. Matthew -- Existence is a convenient concept to designate all of the files that an executable program can potentially process

Re: [PERFORM] Optimize date query for large child tables: GiST or GIN?

leap year, then there is no representation for the 29th of February, so not all data points will have a representative number to insert into the database. Matthew -- No, C++ isn't equal to D. 'C' is undeclared, so we assume it's an int, with a default value of zero. H

Re: [PERFORM] Optimize date query for large child tables: GiST or GIN?

, and maybe even not do partitioning. Matthew -- Trying to write a program that can't be written is... well, it can be an enormous amount of fun! -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to

Re: [PERFORM] Optimize date query for large child tables: GiST or GIN?

hinks that it will be fastest to do a seq scan. In actual fact, for 78 stations, the index would be faster, but for 4046 it wouldn't. If you will be querying by season quite regularly, had you considered partitioning by season? Matthew -- Geography is going places. -- Sent via pgsq

Re: [PERFORM] Optimize date query for large child tables: GiST or GIN?

could create an index on (extract(YEAR FROM m.taken)). Matthew -- Here we go - the Fairy Godmother redundancy proof. -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] merge join killing performance

do the stats say the max values are? Matthew -- Nog: Look! They've made me into an ensign! O'Brien: I didn't know things were going so badly. Nog: Frightening, isn't it? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your sub

Re: [PERFORM] merge join killing performance

that's weird. How is the cost of the merge join only 902, when the cost of one of the branches 157830, when there is no LIMIT? Are the statistics up to date? Matthew -- As you approach the airport, you see a sign saying "Beware - low flying airplanes". There's not a lot y

Re: [PERFORM] Planner not using column limit specified for one column for another column equal to first

te (which sits on top of Postgres and makes use of materialised views to speed up queries) uses a similar approach - it expends effort proportional to the estimated cost of the query, as reported by EXPLAIN. Matthew -- To most people, solutions mean finding the answers. But to chemists, solu

Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index

idx1 on tb1(col1, col2); yup :) For those of you who are not native English speakers, "Yup" is a synonym for "Yes." Matthew -- Richards' Laws of Data Security: 1. Don't buy a computer. 2. If you must buy a computer, don't turn it on. -- Sent via pgsql-performa

Re: [PERFORM] experiments in query optimization

On Tue, 30 Mar 2010, Faheem Mitha wrote: work_mem = 1 GB (see diag.{tex/pdf}). Sure, but define sane setting, please. I guess part of the point is that I'm trying to keep memory low You're trying to keep memory usage low, but you have work_mem set to 1GB? Matthew -- "Prove

Re: [PERFORM] Performance regarding LIKE searches

On Mon, 29 Mar 2010, randa...@bioinfo.wsu.edu wrote: WHERE ... lower(n.name) LIKE 'Scaffold:scaffold_163:1000..1199%' ... I'm sure you noticed that this is never going to return any rows? Matthew -- Me... a skeptic? I trust you have proof? -- Sent via pgsql-performance mail

Re: [PERFORM] Optimizer showing wrong rows in plan

space. Matthew -- If you let your happiness depend upon how somebody else feels about you, now you have to control how somebody else feels about you. -- Abraham Hicks -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] memory question

need and scaring you about wasting resources, than it would cost to just slap 24GB in the machine. 24GB is the least amount of RAM I would consider putting in a new server nowadays. It's so cheap. Matthew -- Lord grant me patience, and I want it NOW! -- Sent via pgsql-performance mailing

Re: [PERFORM] GiST index performance

However, this is beyond the scope of the problem of speeding up gist. Matthew -- If you let your happiness depend upon how somebody else feels about you, now you have to control how somebody else feels about you. -- Abraham Hicks -- Sent via pgsql-performance mailing list (pgsql-performance@postgr

Re: [PERFORM] GiST index performance

;d also love to dive into the core gist code, but am rather daunted by it. I believe that there is something there that is taking more time than I can account for. The indexing algorithm itself is good. Matthew -- "The problem with defending the purity of the English language is that

Re: [PERFORM] too complex query plan for not exists query and multicolumn indexes

uld be more expensive, of course, since it'd have more indexes to maintain. That isn't necessarily so, until the issue referred to in the above linked messages is resolved. It depends. Matthew -- I've run DOOM more in the last few days than I have the last few months. I just love

Re: [PERFORM] pg_dump far too slow

-performance/2009-07/msg00348.php It seems that getting pg_dump to do the compression is a fair amount slower than piping the plain format dump straight through gzip. You get a bit more parallelism that way too. Matthew -- I'm always interested when [cold callers] try to flog conservat

Re: [PERFORM] GiST index performance

On Thu, 25 Feb 2010, Bruce Momjian wrote: Was there every any conclusion on this issue? Not really. Comments inline: Matthew Wakeling wrote: Revisiting the thread a month back or so, I'm still investigating performance problems with GiST indexes in Postgres. Looking at

Re: [PERFORM] Deleting bytea, autovacuum, and 8.2/8.4 differences

What are the implications of using TRUNCATE on a table that has TOASTed data? Is TOAST all stored in one single table, or is it split up by owner table/column name? Might you still end up with a normal delete operation on the TOAST table when performing a TRUNCATE on the owner table? Mat

[PERFORM] Dell PERC H700/H800

SATA or SAS compatible, and they shouldn't be allowed to use those acronyms any more. Matthew -- An optimist sees the glass as half full, a pessimist as half empty, and an engineer as having redundant storage capacity. -- Sent via pgsql-performance mailing list (pgsql-performance@post

Re: [PERFORM] Optimizing Postgresql server and FreeBSD for heavy read and writes

r WAL discs is). If you make sure of that, then there is no reason to get expensive fast discs for the WAL at all (assuming they are expensive enough to not lie about flushing writes properly). Matthew -- So, given 'D' is undeclared too, with a default of zero, C++ is equ

Re: [PERFORM] System overload / context switching / oom, 8.3

t in large queries running slowly, but have the danger of driving the system to swap and OOM. Matthew -- A good programmer is one who looks both ways before crossing a one-way street. Considering the quality and quantity of one-way streets in Cambridge, it should be no surprise that there are so

Re: [PERFORM] Should the optimiser convert a CASE into a WHERE if it can?

On Wed, 27 Jan 2010, Віталій Тимчишин wrote: How about SELECT SUM (case when id > 120 and id < 121 then 1 end) from tbl_tracker; That is very interesting. * All the functions should be noop for null input Alas, not true for COUNT(*), AVG(), etc. Matthew -- An optimist se

Re: [PERFORM] test send (recommended by Dave Page)

ay your email. Helpful, for a "help" mailing list. Matthew -- The early bird gets the worm, but the second mouse gets the cheese. -- 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] Benchmark shows very slow bulk delete

n the comments, I saw this: "The slow times for Postgresql Bulk Modify/Bulk Delete can be explained by foreign key references to the updates table." I'm not sure that fully explains it though, unless there are basically zero rows being deleted - it's hardly bulk then, is it?) Ma

Re: [PERFORM] Should the optimiser convert a CASE into a WHERE if it can?

to a WHERE. In my mind, this is quite a lot of work for the planner to do to solve this one. That translates into quite a lot of work for some poor programmer to do to achieve it. If you have the money, then hire someone to do it! Matthew -- I don't want the truth. I want something

Re: [PERFORM] splitting data into multiple tables

consuming that much of their bandwidth, or even if there are copyright issues involved in grabbing that much of their data? (The other problem with using the word "crore" is that although it may mean 1000 in a few countries, it could also mean 50.) Matthew -- Of course it&#x

Re: [PERFORM] splitting data into multiple tables

ng queries without some very clever software (because not all the data is available on the server), which will probably be hard to manage and slow down the execution anyway. My recommendation would be to stick with a single table unless you have a real need to partition. Matthew -- Note: some

Re: [PERFORM] Sql result b where condition

#x27;Tom' and orig_salary=2413)or(orig_salary=1234 ) ORDER BY orig_salary DESC as there is going to be only two values for orig_salary. Matthew -- The early bird gets the worm. If you want something else for breakfast, get up later. -- Sent via pgsql-performance mailing list (pgsql-p

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

. Matthew -- I wouldn't be so paranoid if you weren't all out to get me!! -- 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

ce is. Matthew -- The third years are wandering about all worried at the moment because they have to hand in their final projects. Please be sympathetic to them, say things like "ha-ha-ha", but in a sympathetic tone of voice -- Computer Science Lec

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

ist server. Someone mentioned something about banning the guy who set the list up from the internet or something. http://archives.postgresql.org/pgsql-performance/2008-01/msg00290.php Matthew -- Bashir: The point is, if you lie all the time, nobody will believe you, even when you'

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

dle RAID array hanging around somewhere. Matthew -- A good programmer is one who looks both ways before crossing a one-way street. Considering the quality and quantity of one-way streets in Cambridge, it should be no surprise that there are so many good programmers there. -- Sent via pgsql-p

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

more into a system, then it starts being an issue. Matthew -- For every complex problem, there is a solution that is simple, neat, and wrong. -- H. L. Mencken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

ought the best strategy for a good RAID controller was NOOP. Agreed. That's what we use here. My observation is though that noop is identical in performance to anticipatory and deadline. Theoretically, it should be faster. Matthew -- "Take care that thou useth the proper method w

Re: [PERFORM] new server I/O setup

smooths random access, but it also accelerates fsync. The whole point of the WAL disc is for it to be able to accept lots of fsyncs very quickly, and it can't do that without its BBU cache. Matthew -- Heat is work, and work's a curse. All the heat in the universe, it's going to coo

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

again, and looking at how busy the CPU on the machine is. The disc may be the bottleneck, or the CPU may be the bottleneck. Matthew -- "Take care that thou useth the proper method when thou taketh the measure of high-voltage circuits so that thou doth not incinerate both thee and the meter;

Re: [PERFORM] new server I/O setup

u can make, but you could consider whether it is sufficient to just have a spare disc sitting on a shelf next to the server rather than using up a slot in the server. Depends on how quickly you can get to the server on failure, and how important the data is. Matthew -- In the beginning was th

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

d someone who knows confirm this? Matthew -- Let's say I go into a field and I hear "baa baa baa". Now, how do I work out whether that was "baa" followed by "baa baa", or if it was "baa baa" followed by "baa"? - Computer Scie

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

icipatory on a RAID array though. It seems to me that CFQ is simply bandwidth limited by the extra processing it has to perform. Matthew -- Experience is what allows you to recognise a mistake the second time you make it. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresq

Re: [PERFORM] Slow "Select count(*) ..." query on table with 60 Mio. rows

esystem is ext3 Ditch lvm. This is an FAQ. Counting the rows in a table is an expensive operation in Postgres. It can't be answered directly from an index. If you want, you can keep track of the number of rows yourself with triggers, but beware that this will slow down write access to the tabl

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

time moving the disc head from one part of the disc to another. Matthew -- No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to yo

Re: [PERFORM] performance config help

I'll use the Plain Text when posting future messages. To be honest, that's always a good idea, although you didn't actually do wrong. I do know people whose spam filters immediately discard emails that contain a HTML alternative - that's taking it to the extreme! Matthew

Re: [PERFORM] performance config help

at the beginning of lines, which most mail readers will add automatically, make all the difference. Matthew -- Me... a skeptic? I trust you have proof? -- 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] Choice of bitmap scan over index scan

not in the cache, by maybe an order of magnitude, depending on your hardware setup. The index scan may perform better at the moment, but the bitmap index scan is safer. Matthew -- Change is inevitable, except from vending machines. -- Sent via pgsql-performance mailing list (pgsql-

Re: [PERFORM] Air-traffic benchmark

to plan. Also, for queries that access small parts of the table, indexes will help more than partitioning will. Partitioning will help most in the case where you want to summarise a single year's data. Not really otherwise. Matthew -- Q: What's the difference between ignorance an

Re: [PERFORM] Digesting explain analyze

table. The copy will become out of date quickly, but it will allow you to see whether the performance benefit is worth it. It will also tell you how long a cluster will actually take, without actually locking anything. Matthew -- In the beginning was the word, and the word was unsigned, and

Re: [PERFORM] pg_connect takes 3.0 seconds

authentication purposes. Matthew -- "To err is human; to really louse things up requires root privileges." -- Alexander Pope, slightly paraphrased -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscrip

Re: [PERFORM] Idea how to get rid of Bitmap Heap Scan

of spindles in the RAID array, or by having gobs of RAM and keeping everything in cache. Matthew -- A good programmer is one who looks both ways before crossing a one-way street. Considering the quality and quantity of one-way streets in Cambridge, it should be no surprise that there are so ma

Re: [PERFORM] Fw: Help me put 2 Gigs of RAM to use

ect starts with the word "help", just in case you're asking for help about managing the mailing list. The default behaviour is not to inform you that it has done so. It is highly annoying - could a list admin please consider changing this? Matthew -- I would like to think th

Re: [PERFORM] Optimizing Bitmap Heap Scan.

assle. Matthew -- Now, you would have thought these coefficients would be integers, given that we're working out integer results. Using a fraction would seem really stupid. Well, I'm quite willing to be stupid here - in fact, I'm going to use complex numbers.

Re: [PERFORM] SSD + RAID

with this one. http://www.theregister.co.uk/2009/12/08/seagate_pulsar_ssd/ Matthew -- The third years are wandering about all worried at the moment because they have to hand in their final projects. Please be sympathetic to them, say things like "ha-ha-ha", but

Re: [PERFORM] Optimizing Bitmap Heap Scan.

s if you are using a RAID array. I'd also try using "SELECT DISTINCT" rather than "GROUP BY" and seeing if that helps. Matthew -- Now the reason people powdered their faces back then was to change the values "s" and "n" in this equation here.

Re: [PERFORM] Order by (for 15 rows) adds 30 seconds to query time

optimise the query? But perhaps the biggest factor here is calling a five table join a "pretty simple query". Matthew -- Prolog doesn't have enough parentheses. -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] Query times change by orders of magnitude as DB ages

pace after vacuuming). Matthew -- for a in past present future; do for b in clients employers associates relatives neighbours pets; do echo "The opinions here in no way reflect the opinions of my $a $b." done; done -- Sent via pgsql-performance mailing list (pgsql-performance@postgresq

Re: [PERFORM] Query times change by orders of magnitude as DB ages

e files in exceptional circumstances, then cluster is the tool for the job. Matthew -- Matthew: That's one of things about Cambridge - all the roads keep changing names as you walk along them, like Hills Road in particular. Sagar: Yes, Sidney Street is a bit like that too.

Re: [PERFORM] Strange performance degradation

On Tue, 24 Nov 2009, Denis Lussier wrote: Bouncing the app will roll back the transactions. Depends on the application. Some certainly use a shutdown hook to flush data out to a database cleanly. Obviously if you kill -9 it, then all bets are off. Matthew -- Software suppliers are trying

[PERFORM] RAID card recommendation

n opinion on whether it is a turkey or a star? Another possibility is a 3-ware card of some description. Thanks in advance, Matthew -- Now you see why I said that the first seven minutes of this section will have you looking for the nearest brick wall to beat your head against. This is why I do

Re: [PERFORM] Strange performance degradation

m in dev/test is the priority, but I would be very cautious about killing transactions in production. You don't know what data is uncommitted. The safest thing to do may be to bounce the application, rather than Postgres. Matthew -- All of this sounds mildly turgid and messy and confu

Re: [PERFORM] [GENERAL] Strange performance degradation

action and thoroughly confuse the client application, you can send a TERM signal to a backend, but the consequences to your data are on your own head. Fix the application, don't tell Postgres to stop being a decent database. Matthew -- I would like to think that in this day and age people would k

Re: [PERFORM] Query times change by orders of magnitude as DB ages

command, then running vacuum full will make the table and index layout worse, not better. Matthew -- Riker: Our memory pathways have become accustomed to your sensory input. Data: I understand - I'm fond of you too, Commander. And you too Counsellor -- Sent via pgsql-performance mailing

Re: [PERFORM] Why is the query not using the index for sorting?

* creating the index and clustering, or before? Matthew -- [About NP-completeness] These are the problems that make efficient use of the Fairy Godmother.-- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Strange performance degradation

hat the data is more in cache, and postgres is able to process it eight times *faster*. Restarting Postgres kills the cache and puts you back at square one. Which of these is it? Matthew -- Reality is that which, when you stop believing in it, doesn't go away.

  1   2   3   4   5   6   7   >