Re: [PERFORM] Performance query about large tables, lots of concurrent access

le partition. I mean, your most active table is also the largest? Seems a bit backward, to me. -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com ---(end of broadcas

Re: [PERFORM] Best way to get the latest revision from a table

, b DESC; Maybe I'm just misunderstanding your situation, though. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for term

Re: [PERFORM] Best way to get the latest revision from a table

> Shaun's example is a bit off: normally, when using DISTINCT ON, you want > an ORDER BY key that uses all the given DISTINCT keys and then some > more. To get the max revision for each a/b combination it ought to be Hah, well i figured I was doing something wrong. I just thought about it a lit

Re: [PERFORM] Best way to get the latest revision from a table

ith the ability to discard non-aggregate results just by screwing around with your sorting. Still one of my favorite tricks. -- Shaun Thomas Peak6 | 141 W. Jackson Blvd. | Suite 800 | Chicago, IL 60604 312-676-8870 stho...@peak6.com __ See http

Re: [PERFORM] Bad plan when join on function

ATILE. Joining on the result of a function will always do this. The database can't know what your function will return. If you can avoid using a function in your join clause, do so. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago

Re: [PERFORM] How to use indexes for GROUP BY

alue to find out which is the "max", which is why using ORDER BY *may* fix your problem. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com

Re: FW: [PERFORM] Queries becoming slow under heavy load

w many queries you're processing per minute or per second pretty easily. We've hit 8600 TPS before and don't have nearly the trouble you've been reporting. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...

Re: [PERFORM] Why I lost the last pg_xlog file?

w xlog. 3. Copy the file from step 1 and anything older than it to your archive/slave. Doing this *may* confuse the built-in archive system if your archive_command is too strict. 4. Profit. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago

Re: [PERFORM] Real vs Int performance

s a 10% boost if just the keys are switched over to INT or BIGINT. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and c

Re: FW: [PERFORM] Queries becoming slow under heavy load

're probably working from memory cache, or getting less requests. There have been times our queries are "slow" and when we check this stat, it's often at or above 90%, sometimes for minutes at a time. That's almost always a clear indicator you have IO contention. Queries can

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

. Hints are not even that complicated to program. Then write a contrib module. It's not part of the core DB, and it probably never will be. This is a *very* old argument. There's literally nothing you can say, no argument you can bring, that hasn't been heard a million t

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

y're doing it wrong, and how they're just another vendor making a database product that can't support massive production databases, is doing nothing but ensuring they'll ignore you. Flies, honey, vinegar, etc. -- Shaun Thomas OptionsHouse

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

rum. Folks, I apologize for ever taking part in this conversation and contributing to the loss of signal to noise. Please forgive me. -- Shaun Thomas Peak6 | 141 W. Jackson Blvd. | Suite 800 | Chicago, IL 60604 312-676-8870 stho...@peak6.com __

Re: [PERFORM] Talking about optimizer, my long dream

seem to enjoy berating the PostgreSQL community as if it owes you something. Also, we don't care if you don't use PostgreSQL. If I put something up for free, some random guy not taking it won't exactly hurt my feelings. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Su

Re: [PERFORM] Really really slow select count(*)

xample, is a boolean. If it's evenly distributed, that's 150k matches for true or false, rendering it useless, yet still requiring space and maintenance. I'm guessing the story is similar for quite a few of the others. It doesn't really ex

Re: [PERFORM] Really really slow select count(*)

t much. Felix, If you're running a version before 8.4, what is your max_fsm_pages setting? If it's too low, autovacuum won't save you, and your tables will continue to grow daily unless you vacuum full regularly, and I wouldn't recommend that to my worst enemy. ;) -

Re: [PERFORM] Really really slow select count(*)

bined were less than 10%. The index was 10x smaller and much faster than before. If you know both booleans are used together often, you can combine them into a single index, again using a partial where it only indexes if both values are true. Much smaller, much faster index if it's mo

Re: [PERFORM] Really really slow select count(*)

space for the larger tables, you see) and the database ended up at less than 20GB. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for

Re: [PERFORM] Really really slow select count(*)

x27;t. If you run an all-database vacuum after-hours, you'll see the stuff at the end. And if your 'page slots are required' is greater than your 'page slots are in use,' you've got a problem. -- Shaun Thomas OptionsHouse | 141 W. Jackso

Re: [PERFORM] Really really slow select count(*)

u installed this database. Tables with the highest turnover were hit hardest, but they all have non-ideal sizes compared to what they would be if your maintenance was working. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com

Re: [PERFORM] Really really slow select count(*)

every time I use it. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql

Re: [PERFORM] Really really slow select count(*)

like your DB is big enough to benefit from that. Later versions have made 100 the default, so you'd just be catching up. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 01:59 PM, felix wrote: still no advice on the pages I think it just hates you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com

Re: [PERFORM] Really really slow select count(*)

you just do a delete, the table won't change at all, except that it'll have less active records. well just think how sprightly my website will run tomorrow once I fix these. Maybe. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 |

Re: [PERFORM] Really really slow select count(*)

You really got screwed by the default settings. You don’t actually need to “hack” the kernel, but you do have to make these changes, because the amount of memory PG has on your system is laughable. That might actually be the majority of your problem. In your /etc/sysctl.conf, you need these lin

Re: [PERFORM] Really really slow select count(*)

> I checked the site, everything was normal. I went in via psql and tried some > queries for about half an hour and continued to monitor the site. then I went > to bed at 7am (EU time). > > Why did it shutdown so much later ? That’s one of the things I talked about. To be safe, PG will start to

Re: [PERFORM] Really really slow select count(*)

ou have no choice because your company doesn't have an official DBA. Either way, you'll need to know this stuff. Which is why we're helping out. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com _

Re: [PERFORM] Why we don't want hints Was: Slow count(*) again...

e optimizer out. Really... how difficult would it be to add that syntax to the JOIN statement, for example? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com

Re: [PERFORM] Why we don't want hints Was: Slow count(*) again...

nd their Oracle-style syntax, and the only thing he said about that was to use it as further evidence that PostgreSQL should implement them. I'm very tempted to say he wanted something for free, and was angry he couldn't get it. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suit

Re: [PERFORM] Why we don't want hints Was: Slow count(*) again...

sting concept. Tell the optimizer what you want and how the data is really related in cases where it's wrong, and let it figure out the best path. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...

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

ould remove the necessity of the first wildcard, which is really what's hurting you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaim

Re: [PERFORM] LIMIT on partitioned-table!?

tition definition, you're not helping yourself. The main issue might just be that you've used an order clause. LIMIT 1000 or not, even if it can restrict the result set based on your CHECK criteria, it'll still need to select every matching row from every matched partition, order

Re: [PERFORM] Unused indices

n general, but this will find all the "safe" indexes for removal. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms an

Re: [PERFORM] Unused indices

trick fail, but from your perspective of working with so many variations, I could see how you'd want to avoid it. I'll be good from now on. ;) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __

Re: [PERFORM] Picking out the most recent row using a time stamp column

) as filtered_table WHERE ranking = 1 Why did you use row_number instead of rank? I am now curious how the speed compares though. I still think the DISTINCT ON will be faster, but it would be a great surprise. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL

Re: [PERFORM] Table partitioning problem

plication to scale horizontally via data sharding or some other shared-nothing cluster with plProxy, GridSQL or PGPool. You'll have this problem with any modern database. Big tables are a pain in everybody's asses. It's too bad PostgreSQL can't assign one thread per da

Re: [PERFORM] Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3

d, and I've run into it on occasion for effectively the entire history of PostgreSQL. Normally increasing the statistics on the affected columns and re-analyzing fixes it, but on a composite index, that won't necessarily be the case. -- Shaun Thomas OptionsHouse | 141 W. Jackson

Re: [PERFORM] Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3

te to associate with the tablespace, if nothing else. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions relat

Re: [PERFORM] good old VACUUM FULL

ow you don't want to "experiment" with kernel parameters, but you *need* to increase your max_fsm_pages setting. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www

Re: [PERFORM] Re-Reason of Slowness of Query

UP BY p.crawled_page_id; Or if you like the cleaner query without a sub-select: EXPLAIN ANALYZE SELECT p.crawled_page_id FROM page_content p LEFT JOIN clause2 c ON (c.source_id = p.crawled_page_id) WHERE c.source_id IS NULL GROUP BY p.crawled_page_id; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. |

Re: [PERFORM] Re-Reason of Slowness of Query

ng it out of so much old code it's been fused to my DNA. Actually, we're still on 8.2 so... :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email

Re: [PERFORM] maintenance_work_mem + create index

d this way, so we're not at any risk with 64GB on our main nodes. We already run a custom init.d script anyway because we needed something LSB compatible for Pacemaker. I highly recommend it. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-6

Re: [PERFORM] maintenance_work_mem + create index

config file. What I meant was that /dev/shm automatically exists on our Linux box and we make use of it. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com

Re: [PERFORM] Why Index is not used

ting back 10,000 rows, or even a million rows, your query could execute in a fraction of the time. But joining every row in both tables and returning a 30-million row result set isn't going to be fun for anyone. Are you actually processing all 30-million rows you get back? Storin

[PERFORM] Confirmation of bad query plan generated by 7.4 tree

anning 4 years. 2 days should come back instantly. Both tables are freshly vacuumed and analyzed, so I'll just chalk this up to 7.4 sucking unless someone says otherwise. -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847

Re: [PERFORM] Confirmation of bad query plan generated by 7.4

generated a much more accurate set of estimates. I have to assume that 7.4 is incapable of that optimization step. Now that I know this, I plan on modifying my stored proc to calculate the value before inserting it into the query. Thanks! -- Shaun Thomas Database Administrator Leapfrog Online

Re: [PERFORM] Confirmation of bad query plan generated by 7.4

; What's your stats target set to for that table? Not sure what you mean by that. It's just that this table has 27M rows extending over 4 years, and I'm not quite sure how to hint to that. An index scan for a few days would be a tiny fraction of the entire table, so PG being insi

Re: [PERFORM] Confirmation of bad query plan generated by 7.4

s (like when I replace lastTime with the text equivalent), it takes 10 minutes... I can see the planner not liking the results of a function, but a variable? That's a static value! ::cry:: -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston,

Re: [PERFORM] Confirmation of bad query plan generated by 7.4

required. I was just surprised at the large difference in manual execution as opposed to the SP with the same query. > We've occasionally debated ways to do it better, but no such > improvement will ever appear in 7.4 ;-) Agreed! When we finally upgrade, I fully plan on putting a symboli

Re: [PERFORM] any hope for my big query?

lbumjoins. From there, add on the track information, and use that to restrict the matching nodes. Your explain should be better with the above. Just remember with the levenshtein in there, you're forcing a sequence scan on the node table. Depending on how big that table is, you may not b

Re: [PERFORM] Hints proposal

ee is some way of directly addressing the query-planner's API and circumvent SQL entirely for really nasty or otherwise convoluted result-sets, but of course I know that's rather unreasonable. C'mon, some of us DBAs have math degrees and know set theory... ;) -- Shaun Thomas Databas

Re: [PERFORM] Performance Optimization for Dummies 2 - the SQL

ow about an "IN (blabla LIMIT 1)" ? -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com ---(end of broadcast)--- TIP 3: Have you check

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

you also cluster them, so a backwards index scan is almost always the wrong answer. Personally I consider this a bug, and it's been around since at least the 8.1 tree. The only real answer is that you have a fast version of the query, so try and play with it until it acts the way you want. -- Shaun T

Re: [PERFORM] need to speed up query

ed summary table that contains this information (and maybe more) maintained by a trigger or regularly invoked stored-procedure and then you can select from *that* with much less agony. Then there's fact-tables, but that's beyond the scope of this email. ;) Good luck! -- Shaun Thomas Dat

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

about not having query hints or being able to tell Postgres to never, ever, ever index-scan certain tables. :) -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com -- Sent via pgsql-performanc

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

hen you have an order clause and are expecting a ton of results, (say you're getting the first 25 rows of 1 or something). But if it would be faster to generate the results and *then* sort, but Postgres thinks otherwise, you're pretty much screwed. But that's the long answer. Yo

Re: [PERFORM] Creating a foreign key

h a little tweaking here and there to make the query-planner happy. But that's the long version. Postgres is by no means bare-bones, but it assumes DBAs are smart enough to manage the structures they bolt onto the metal. :) -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwoo

Re: [PERFORM] Multiple index builds on same table - in one sweep?

ript just launches them and makes a log directory so you can watch the progress. I've run this with up to 16 concurrent threads without major issue. It comes in handy. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 st

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

as are stats on your joined id for this particular query, and you're basically telling to join all of them. That usually calls for a sequence scan, because millions of index seeks will almost always be slower than a few sequence scans. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. |

Re: [PERFORM] postgresql random io test with 2 SSD Kingston V+100 500GB in (software) Raid1

other SSDs of that generation. I can't imagine that would be very good for database usage patterns by comparison. Especially with that aggressive garbage collection. I mean... an old Indilinx OCZ Vertex has better performance than that. -- Shaun Thomas OptionsHouse | 141 W. Jackson Bl

Re: [PERFORM] amazon ec2

/message/65648/ The consensus seems to be that Amazon's cloud is fine... so long as you stay far, far away from EBS. Apparently that needs a little more work. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak

Re: [PERFORM] Benchmarking a large server

ional 20-30% spikes. Even that's an unfair comparison in favor of the RAID, because we added DRBD to the mix because you can't share a PCI card between two servers. If you do have two 1.3TB Duo cards in a 4x640GB RAID-10, you should get even better read times than we did. -- Shaun T

Re: [PERFORM] Benchmarking a large server

ry and caching the entire dataset is only an improvement. -- Shaun Thomas Peak6 | 141 W. Jackson Blvd. | Suite 800 | Chicago, IL 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to t

Re: [PERFORM] partition query on multiple cores

nodes cut execution time in half. It's meant for server parallelism, so I wouldn't necessarily recommend splitting your data up across nodes on the same server. But it seems to deliver as promised when used in the right circumstances. -- Shaun Thomas OptionsHouse | 141 W. Jacks

Re: [PERFORM] 'Interesting' prepared statement slowdown on large table join

start badly skewing query plans. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sen

Re: [PERFORM] Postgres refusing to use >1 core

op, reload, and index the entire table faster than that. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions relate

Re: [PERFORM] Postgres refusing to use >1 core

mance' and they're all specific examples taken from day-to-day queries and jobs here, all from different categories of mistake. It's just a part of being a good DBA. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho..

Re: [PERFORM] Postgres refusing to use >1 core

on system monitoring or allocation tests. Little things Senior DBAs might know after experiencing them, or reading lists like this one. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ S

Re: [PERFORM] Postgres refusing to use >1 core

h. ;) But the stats are all pretty darn close, so far as I can tell. The only thing that's off is the group aggregate... by about two orders of magnitude. So I just chalked it up to 8.2 being relatively horrible, and punted to just using a temp table to trick the optimizer into doing it rig

Re: [PERFORM] Postgres refusing to use >1 core

y for exactly that reason. But what I found odd is that in my rush to execute this for the end of day reports, I forgot that step, and it still ran fine. I've found that the planner tends to treat un-analyzed tables somewhat pessimistically, which is fine by me. -- Shaun Thomas OptionsHou

Re: [PERFORM] [OT]: Confidentiality disclosures in list posts (Was: SORT performance - slow?)

sent to an internal recipient. You can see this for yourselves in this message, as my company's little blurb gets attached after my signature lines. It's just standard boilerplate meant as a CYA measure, really. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL

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

Increase your checkpoint_segments. If you see "checkpoint starting: xlog" instead of "checkpoint starting: time", you don't have enough checkpoint segments to handle your writes. Checkpoints *will* degrade your throughput. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd.

Re: [PERFORM] picking a filesystem

ount -o allocsize=256m,logbufs=8,noatime,nodiratime,attr2,logbsize=256k Maybe more recent XFS kernels have other options we're not aware of, but we've had good luck with these so far. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago

Re: [PERFORM] Problem query

7;ll still get a sequence scan from these, however. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions relat

Re: [PERFORM] Problem query

the two unique column sets, and it's possible it represents combinations that don't exist. But I suppose a late EXISTS pass could solve that problem. I assume there's an easier way to do that. In either case, when is PG getting loose index scans? ;) -- Shaun Thomas OptionsHous

Re: [PERFORM] Set of related slow queries

re where clauses. If you need reports on a lot of this data on a regular basis, consider running a nightly or hourly batch to insert them into a reporting table you can check later. There's a lot you can do here. -- Shaun Thomas OptionsHouse |

Re: [PERFORM] seq scan in the case of max() on the primary key column

x27;tcpsessions', 'id'); Someone probably has a better solution. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for ter

Re: [PERFORM] seq scan in the case of max() on the primary key column

further inheritance. It's rare, but possible. This function will always get you the max value on a column in a series of partitioned tables, and quickly so long as it's indexed. It's a bit of a hack, but it's worked fine for us while we wait for the planner to catch up. :

Re: [PERFORM] seq scan in the case of max() on the primary key column

On 06/17/2011 03:31 PM, Jim Nasby wrote: c_parent_oid CONSTANT oid := (p_parent_schema || '.' || p_parent_table )::regclass; Well isn't *that* a handy bit of magic. How did I not know about that? Thanks! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Ch

Re: [PERFORM] seq scan in the case of max() on the primary key column

y function. :) EDB 8.3 (which is really PostgreSQL 8.2) doesn't have a regclass->text conversion. But I'll bookmark the wiki page anyway, so I can update my function after upgrading. Heh. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676

Re: [PERFORM] Contemplating SSD Hardware RAID

numbers and think to themselves "What if that were in a RAID?" Right now, drives aren't quite there yet, or the ones that are cost more than most want to spend. It's a shame, really. But I'm willing to wait it out for now. -- Shaun Thomas OptionsHouse | 141 W. Jackson

Re: [PERFORM] seq scan in the case of max() on the primary key column

cause we decided to skip 8.4 in favor of 9.0. And as we use EDB instead of PostgreSQL directly, our 8.3 is actually 8.2. Got that? ;) Sorry for the confusion. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@p

Re: [PERFORM] Cost of creating an emply WAL segment

x27;s even way beyond an OS-level dirty buffer flush on a massive system. Wow! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for

Re: [PERFORM] is parallel union all possible over dblink?

in. I wouldn't use it for anything but a reporting database that can be reconstructed if necessary. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www

Re: [PERFORM] Infinite Cache

ll it takes to bring down a heavy OLTP system. I'd say it's probably safe enough these days. But it's also one of those exclusive selling points they're using right now to garner EDB customers. So I doubt it'll be released any time *soon*, though may make it event

[PERFORM] Just a note about column equivalence disarming the planner

have done so. I've noticed lots of little things like this recently, and I have to say, the planner has made huge improvements regardless of what perception may reflect sometimes. It still has some holes and room for improvement, but I just wanted to thank the devs for all their hard work.

Re: [PERFORM] Postgres 8.4 memory related parameters

abled up to at least 5x the number of cores. I was honestly shocked at those results, but they were consistent across multiple machines from two separate vendors. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-88

Re: [PERFORM] Postgres 8.4 memory related parameters

75s cores against our old E7450s. The scary part was that a dual X5675 ended up being 2.5x faster than a quad E7450 at 24-user concurrency. It's unreal. It's a great way to save on per-core licensing fees. We're also on an 8.2 database. We're upgrading soon, I promise. :) -- Sha

Re: [PERFORM] Postgres 8.4 memory related parameters

s peaked at 24-concurrency. At that concurrency, HT was 60% faster than non-HT. Sorry if I mixed my terminology. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http:

Re: [PERFORM] Postgres 8.4 memory related parameters

ccount for the fact disks suck. :p -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email --

Re: [PERFORM] Postgres 8.4 memory related parameters

n't really notice further tweaking, and a really active system would converge to cpu count through a pooler and be cached to the gills anyway. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com ___

Re: [PERFORM] Summaries on SSD usage?

abase during a high transactional period. Lemme tell ya... no RAID-10 in the world can supply 12k TPS with little to no warning. A good set of SSDs or PCI cards can. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-88

Re: Fwd: [PERFORM] Summaries on SSD usage?

se, and not storage capacity. Some vendors have demo hardware they'll let you use to determine if it applies to your case, so you might want to contact FusionIO, RAMSAN, Virident, or maybe OCZ. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

) for even more win. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email -- Sent via pgsql-perf

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

ction traffic by 60%, and it greatly improved the database's overall health. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email-disclaimer/ for terms

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

raction of their former length. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email -- Sent

Re: [PERFORM] Allow sorts to use more available memory

emory, is a very, very bad idea. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email -- Sen

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

t. My point stands, though. Don't go crazy with transactions until you know your config can stand up to it, and reduce if possible. We found some tweak points that drastically reduced transaction count with no detrimental effect on the app itself, so we jumped on them. -- Shaun Th

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

, but it's certainly not corruption. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email --

Re: [PERFORM] PG 9 adminstrations

't want the complaints about tables or indexes or constraints that already exist, use the -a option for data-only dumps. Also, might I suggest using the pgsql-novice list? They're more likely to help with general issues like this. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. |

  1   2   3   >