On Tue, 4 Dec 2007, Mark Mielke wrote:
This is bikeshed land, right?
I am only interested by juicy projects that have a hope of success. This
subject does interest me - I am hoping my devil's advocate participation
encourages people to seek a practical implementation that will benefit me.
Na
Gregory Stark wrote:
> "Julian Mehnle" <[EMAIL PROTECTED]> writes:
> > I actually do have constraints on all the partitions, e.g. for week
> > 34: [...]
> >
> > Shouldn't this be enough to give the query planner a clue that it
> > only has to join the "email" and "email_extras" tables' partitions
>
James Mansion wrote:
Mark Mielke wrote:
PostgreSQL or the kernel should already have the hottest pages in
memory, so the value of doing async I/O is very likely the cooler
pages that are unique to the query. We don't know what the cooler
pages are until we follow three tree down.
I'm assumin
"Julian Mehnle" <[EMAIL PROTECTED]> writes:
> I actually do have constraints on all the partitions, e.g. for week 34:
>
> Check constraints [for email_2007_week34]:
> "email_2007_week34_ts_check" CHECK (ts >= '2007-08-20
> 00:00:00'::timestamp without time zone AND ts < '2007-08-27
> 00:00
Mark Mielke wrote:
PostgreSQL or the kernel should already have the hottest pages in
memory, so the value of doing async I/O is very likely the cooler
pages that are unique to the query. We don't know what the cooler
pages are until we follow three tree down.
I'm assuming that at the time we
James Mansion wrote:
Mark Mielke wrote:
At a minimum, this breaks your query into: 1) Preload all the index
pages you will need
Isn't this fairly predictable - the planner has chosen the index so it
will be operating
on a bounded subset.
What is the bounded subset? It is bounded by the value.
Gregory Stark wrote:
> There are a few things going on here.
>
> 1) The optimizer can't build a plan which ignores those partitions
> because the statistics are just approximations. You could insert into
> one of them at any time and the statistics won't update immediately. If
> you have a partitio
"Julian Mehnle" <[EMAIL PROTECTED]> writes:
> However, if I restrict the query to just the partitions that actually do
> have data in them ...
There are a few things going on here.
1) The optimizer can't build a plan which ignores those partitions because the
statistics are just approximations.
Mark Mielke wrote:
At a minimum, this breaks your query into: 1) Preload all the index
pages you will need
Isn't this fairly predictable - the planner has chosen the index so it
will be operating
on a bounded subset.
, 2) Scan the index pages you needed
Yes, and AIO helps when you can scan the
Julian Mehnle wrote:
> I have a large database with e-mail meta-data (no bodies) for over 100
> million messages. I am running PostgreSQL 8.2.4 on a server with 2GB
> of RAM (shared_buffers = 240MB, temp_buffers = 128MB, work_mem = 256MB,
> maintenance_work_mem = 256MB). I have the data split in
Hi all,
I have a large database with e-mail meta-data (no bodies) for over 100
million messages. I am running PostgreSQL 8.2.4 on a server with 2GB of
RAM (shared_buffers = 240MB, temp_buffers = 128MB, work_mem = 256MB,
maintenance_work_mem = 256MB). I have the data split in two separate
tables,
"Matthew" <[EMAIL PROTECTED]> writes:
> On Tue, 4 Dec 2007, Mark Mielke wrote:
>> So much excitement and zeal - refreshing to see. And yet, no numbers! :-)
>
> What sort of numbers did you want to see?
FWIW I posted some numbers from a synthetic case to pgsql-hackers
http://archives.postgresql.o
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
why does it have different plans for different values
Because the values occur different numbers of times (or so it thinks
anyway). If the rowcount estimates are far from reality, perhaps
increasing the statistics target woul
On Tue, 4 Dec 2007, Mark Mielke wrote:
> So much excitement and zeal - refreshing to see. And yet, no numbers! :-)
What sort of numbers did you want to see?
> You describe a new asynchronous I/O system to map integers to Java
> objects above. Why would you write this? Have you tried BerkeleyDB or
Pallav Kalva <[EMAIL PROTECTED]> writes:
>why does it have different plans for different values
Because the values occur different numbers of times (or so it thinks
anyway). If the rowcount estimates are far from reality, perhaps
increasing the statistics target would help. However, since yo
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
Fwiw, what made you bring up this topic now? You're the second person in about
two days to bring up precisely this issue and it was an issue I had been
planning to bring up on -hackers as it was.
I only just joined the performance
On Tue, 4 Dec 2007, Gregory Stark wrote:
> Fwiw, what made you bring up this topic now? You're the second person in about
> two days to bring up precisely this issue and it was an issue I had been
> planning to bring up on -hackers as it was.
I only just joined the performance mailing list to talk
Matthew wrote:
On Tue, 4 Dec 2007, Mark Mielke wrote:
The larger the set of requests, the closer the performance will scale to
the number of discs
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
T
James Mansion wrote:
Mark Mielke wrote:
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
Why doesn't it help to issue IO ahead-of-time requests when you are
scanning an index? You can read-ahead
in index pages, a
Hi,
Postgres 8.2.4 is not using the right plan for different values.
From the below queries listing.addressvaluation table has 19million
records , the other table listing.valuationchangeperiod is just lookup
table with 3 records.
If you can see the explain plans for the statements
On Tue, 4 Dec 2007, Mark Mielke wrote:
> > The larger the set of requests, the closer the performance will scale to
> > the number of discs
>
> This assumes that you can know which pages to fetch ahead of time -
> which you do not except for sequential read of a single table.
There are circumstanc
Mark Mielke wrote:
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
Why doesn't it help to issue IO ahead-of-time requests when you are
scanning an index? You can read-ahead
in index pages, and submit requests f
[EMAIL PROTECTED] wrote:
So, if you hand requests one by one to the disc, it will almost always be
faster to order them. On the other hand, if you hand a huge long list of
requests to a decent SCSI or SATA-NCQ disc in one go, it will reorder the
reads itself, and it will do it much better than yo
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
Also, it's true, you need to preread more than 12 blocks to handle a 12-disk
raid. My offhand combinatorics analysis seems to indicate you would expect to
need to n(n-1)/2 blocks on average before you've hit all the blocks. There's
littl
Fwiw, what made you bring up this topic now? You're the second person in about
two days to bring up precisely this issue and it was an issue I had been
planning to bring up on -hackers as it was.
"Matthew" <[EMAIL PROTECTED]> writes:
> Kind of. The system cache is just a method to make it simple
On Tue, 4 Dec 2007, Gregory Stark wrote:
> Also, it's true, you need to preread more than 12 blocks to handle a 12-disk
> raid. My offhand combinatorics analysis seems to indicate you would expect to
> need to n(n-1)/2 blocks on average before you've hit all the blocks. There's
> little penalty to
On Tue, 4 Dec 2007, Mark Mielke wrote:
> The disk head has less theoretical distance to travel if always moving
> in a single direction instead of randomly seeking back and forth.
True... and false. The head can move pretty quickly, and it also has
rotational latency and settling time to deal with
"Mark Mielke" <[EMAIL PROTECTED]> writes:
> Matthew wrote:
>
>> I don't think you would have to create a more intelligent table scanning
>> algorithm. What you would need to do is take the results of the index,
>> convert that to a list of page fetches, then pass that list to the OS as
>> an asyn
Matthew wrote:
On Tue, 4 Dec 2007, Mark Mielke wrote:
The bitmap scan method does ordered reads of the table, which can
partially take advantage of sequential reads. Not sure whether bitmap
scan is optimal for your situation or whether your situation would allow
this to be taken advantage of.
On Tue, 4 Dec 2007, Mark Mielke wrote:
> The bitmap scan method does ordered reads of the table, which can
> partially take advantage of sequential reads. Not sure whether bitmap
> scan is optimal for your situation or whether your situation would allow
> this to be taken advantage of.
Bitmap scan
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
"Matthew" <[EMAIL PROTECTED]> writes
Does Postgres issue requests to each random access in turn, waiting for
each one to complete before issuing the next request (in which case the
performance will not exceed that of a single disc), or
On Sat, 1 Dec 2007, Jonah H. Harris wrote:
> I believe the threads you're talking about were related to scanning,
> not parallel query. Though, when Qingqing and I were discussing
> parallel query a little over a year ago, I do seem to recall several
> uninformed opinions stating that sequential s
On Tue, 4 Dec 2007, Gregory Stark wrote:
> "Matthew" <[EMAIL PROTECTED]> writes:
>
> > Does Postgres issue requests to each random access in turn, waiting for
> > each one to complete before issuing the next request (in which case the
> > performance will not exceed that of a single disc), or does
"Matthew" <[EMAIL PROTECTED]> writes:
> Does Postgres issue requests to each random access in turn, waiting for
> each one to complete before issuing the next request (in which case the
> performance will not exceed that of a single disc), or does it use some
> clever asynchronous access method to
I have a question about how Postgres makes use of RAID arrays for
performance, because we are considering buying a 12-disc array for
performance reasons. I'm interested in how the performance scales with the
number of discs in the array.
Now, I know that for an OLTP workload (in other words, lots
On Mon, 2007-12-03 at 15:50 -0800, Joshua D. Drake wrote:
> >
> > Note also some of the more popular pg support companies also offer
> > personal training, even if it isn't advertised. HTH.
>
> Right, I believe some even offer (besides CMD) custom training.
> 2ndQuandrant (sp?) for example recent
36 matches
Mail list logo
