Re: [PERFORM] copy command and blobs

i was looking at http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html when they talk about using OID type to store large blobs (in my case .jpg files ) On Thu, Jan 20, 2011 at 3:17 PM, Mladen Gogala wro

Re: [PERFORM] Fun little performance IMPROVEMENT...

> > My guess is its something hypervisor related. If this happened on direct > hardware I'd be more surprised. Hypervisors have all sorts of stuff going > on, like throttling the number of CPU cycles a vm gets. In your idle > case, your VM might effectively occupy 1Ghz of a CPU, but 2Ghz in the

Re: [PERFORM] Fun little performance IMPROVEMENT...

> > Odd. Did'ja by chance run the select more than once... maybe three or > four times, and always get the same (or close) results? > > Is the stress package running niced? > > -Andy > I got a little crazy, and upgraded the DB to 8.4.5. It still reacts the same. I am hoping someone has an idea

Re: [PERFORM] Fun little performance IMPROVEMENT...

On 1/21/11 12:23 PM, "gr...@amadensor.com" wrote: >> gr...@amadensor.com writes: >>> Here is the fun part. When running 8 threads spinning calculating >>> square >>> roots (using the stress package), the full scan returned consistently >>> 60% >>> faster than the machine with no load. >> >> P

Re: [PERFORM] Fun little performance IMPROVEMENT...

> gr...@amadensor.com writes: >> Here is the fun part. When running 8 threads spinning calculating >> square >> roots (using the stress package), the full scan returned consistently >> 60% >> faster than the machine with no load. > > Possibly the synchronized-seqscans logic kicking in, resulting

Re: [PERFORM] Fun little performance IMPROVEMENT...

> > Odd. Did'ja by chance run the select more than once... maybe three or > four times, and always get the same (or close) results? > > Is the stress package running niced? > The stress package is not running niced. I ran it initially 5 times each. It was very consistent. Initially, I just ran

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

"Kevin Grittner" writes: > Tom Lane wrote: >> If it should be less than 1, then what? > 1 - (estimated tuples / estimated distinct values) ? Uh, no. The number we're after is the probability that an outer tuple has at least one unequal value in the inner relation. This is not 1 minus the pro

Re: [PERFORM] Fun little performance IMPROVEMENT...

gr...@amadensor.com wrote: This was on a 4 CPU Xen virtual machine running 8.1.22 on CENTOS. You're not going to get anyone to spend a minute trying to figure what's happening on virtual hardware with an ancient version of PostgreSQL. If this was an actual full test case against PostgreSQ

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

Tom Lane wrote: > If it should be less than 1, then what? 1 - (estimated tuples / estimated distinct values) ? -Kevin -- 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] Fun little performance IMPROVEMENT...

gr...@amadensor.com writes: > Here is the fun part. When running 8 threads spinning calculating square > roots (using the stress package), the full scan returned consistently 60% > faster than the machine with no load. Possibly the synchronized-seqscans logic kicking in, resulting in this guy no

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

Robert Haas writes: > On Thu, Jan 20, 2011 at 2:05 AM, Achilleas Mantzios > wrote: >> -> Hash Semi Join (cost=2768.00..5671.67 rows=1 width=12) (actual >> time=39.249..81.025 rows=1876 loops=1) >> Hash Cond: (msold.marinerid = msold2.marinerid) >> Join Filter: ((msold2.

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

Distinct on is working really well! If I need to be able to index something I might start thinking along those lines. On Fri, Jan 21, 2011 at 12:13 PM, Robert Haas wrote: > On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett > wrote: > > > > > > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner > >

Re: [PERFORM] Fun little performance IMPROVEMENT...

On 1/21/2011 12:12 PM, gr...@amadensor.com wrote: I was doing a little testing to see how machine load affected the performance of different types of queries, index range scans, hash joins, full scans, a mix, etc. In order to do this, I isolated different performance hits, spinning only CPU, loa

[PERFORM] Fun little performance IMPROVEMENT...

I was doing a little testing to see how machine load affected the performance of different types of queries, index range scans, hash joins, full scans, a mix, etc. In order to do this, I isolated different performance hits, spinning only CPU, loading the disk to create high I/O wait states, and us

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

On 1/21/2011 12:51 PM, Robert Haas wrote: I am starting to suspect that there is a bug in the join selectivity logic in 9.0. We've had a few complaints where the join was projected to return more rows than the product of the inner side and outer side of the join, which is clearly nonsense. I re

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

On Fri, Jan 21, 2011 at 12:42 PM, Mladen Gogala wrote: > On 1/21/2011 12:09 PM, Robert Haas wrote: >> >> Looks like the bad selectivity estimate there is what's killing it. >> Not sure I completely understand why 9.0.2 is coming up with such a >> bad estimate, though. >> > > I would recommend sett

Re: [PERFORM] the XID question

On Thu, Jan 20, 2011 at 12:04 PM, Kevin Grittner wrote: > "Charles.Hou" wrote: > >> my postgresql version is 8.1.3 > > Ouch!  That's getting pretty old; I hope it's not on Windows. > > http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy > > http://www.postgresql.org/about/news.865 >

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

On 1/21/2011 12:09 PM, Robert Haas wrote: Looks like the bad selectivity estimate there is what's killing it. Not sure I completely understand why 9.0.2 is coming up with such a bad estimate, though. I would recommend setting default_statistics_target to 1024 and effective cache size to 20480

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

On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett wrote: > > > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner > wrote: >> >> Tom Lane wrote: >> >> > Shaun's example is a bit off >> >> > As for speed, either one might be faster in a particular >> > situation. >> >> After fixing a mistake in my te

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

On Thu, Jan 20, 2011 at 2:05 AM, Achilleas Mantzios wrote: >                                                     ->  Hash Semi Join   > (cost=2768.00..5671.67 rows=1 width=12) (actual time=39.249..81.025 rows=1876 > loops=1) >                                                           Hash Cond: