On Mon, 2011-01-24 at 12:33 -0700, Scott Marlowe wrote:
> As another poster observed, you're running an ancient version of pgsql
> from a performance perspective. Upgrading to 8.4 or 9.0 would make a
> huge difference in overall performance, not just with one or two
> queries.
Thanks for the tri
Folks,
I'm doing a postmortem on an 8.3 database which recently had to be
rebuilt. The database was over 200% bloated ... 176GB as opposed to
dump/reload size of 55GB. What I find really interesting is *which*
tables were bloated. Consider these two tables, for example, which
consist of one row
Thanks heaps for the advice. I will do some benchmarks to see how long it takes
to cluster all of the database tables.
Cheers,
Jeremy
-Original Message-
From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
Sent: Tuesday, 25 January 2011 1:02 p.m.
To: Jeremy Palmer; Tom Lane
Cc: Rob
Jeremy Palmer wrote:
> My only question is how often will I need to re-cluster the
> table, because it comes at quite a cost.
I probably should have mentioned that the CLUSTER will run faster if
the data is already mostly in the right sequence. You'll be doing a
nearly sequential pass over th
Jeremy Palmer wrote:
> Kevin I've now clustered the table. And the performance did
> increase quite a bit.
Yeah, that's enough to notice the difference.
> My only question is how often will I need to re-cluster the table,
> because it comes at quite a cost. The setup I'm running will mean
>
Might be a chance on 9.0 in a couple of weeks, when I do an upgrade on one our
dev boxes.
Kevin I've now clustered the table. And the performance did increase quite a
bit. My only question is how often will I need to re-cluster the table, because
it comes at quite a cost. The setup I'm running
On Mon, Jan 24, 2011 at 01:29:01PM -0500, Dimi Paun wrote:
> Hi folks,
>
> I have a table like so:
>
> create table tagRecord (
> uid varchar(60) primary key,
> [bunch of other fields]
> location varchar(32),
> creationTStimestamp
> );
> create index idx_tagda
On Mon, Jan 24, 2011 at 11:29 AM, Dimi Paun wrote:
Two very quick points:
> tts_server_db=# explain analyze select location, max(creationTS) from
> tagrecord group by location;
> QUERY PLAN
> -
On 01/24/2011 12:29 PM, Dimi Paun wrote:
I want to simply get the latest "creationTS" for each location,
but that seems to result in a full table scan:
tts_server_db=# explain analyze select location, max(creationTS) from
tagrecord group by location;
Try this, it *might* work:
select DISTINC
Hi folks,
I have a table like so:
create table tagRecord (
uid varchar(60) primary key,
[bunch of other fields]
location varchar(32),
creationTStimestamp
);
create index idx_tagdata_loc_creationTS on tagRecord(location, creationTS);
The number of individual v
Tom Lane wrote:
> Um, that looks like 90 usec per row, not msec.
Oh, right. Well, having to do a random heap access for 1% of the
rows would pretty much explain the run time, then.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
"Kevin Grittner" writes:
> Robert Haas wrote:
>> But isn't 13.5 seconds awfully slow to scan 149557 rows? The sort
>> is sorting 23960kB. Dividing that by 149557 rows gives ~169
>> bytes/per row
> You're right. I would expect 9 ms as per tuple as a worst case if
> it doesn't need to go to TO
Robert Haas writes:
> But isn't 13.5 seconds awfully slow to scan 149557 rows?
Depends on how many physical blocks they're scattered across, which
is hard to tell from this printout. And on how many of the blocks
are already in cache, and what sort of disk hardware he's got, etc.
> Any chance t
Robert Haas wrote:
> But isn't 13.5 seconds awfully slow to scan 149557 rows? The sort
> is sorting 23960kB. Dividing that by 149557 rows gives ~169
> bytes/per row
You're right. I would expect 9 ms as per tuple as a worst case if
it doesn't need to go to TOAST data. Caching, multiple rows
On Mon, Jan 17, 2011 at 11:48 AM, Kevin Grittner
wrote:
> Jeremy Palmer wrote:
>
>> WHERE (
>> (_revision_created <= 16
>> AND _revision_expired > 16
>> AND _revision_expired <= 40)
>> OR (_revision_created > 16
>> AND _revision_created <= 40))
>
>> -> Bitmap Heap
Στις Friday 21 January 2011 22:22:24 ο/η Tom Lane έγραψε:
> "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 l
16 matches
Mail list logo
