Please keep the list cc'd. Gokulakannan Somasundaram wrote: > On 10/23/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: >> Gokulakannan Somasundaram wrote: >> I have also enabled the display of Logical Reads. In order to see that, >> set >>> log_statement_stats on. >> You should start benchmarking, to verify that you're really getting the >> kind of speed up you're looking for, before you spend any more effort on >> that. Reduction in logical reads alone isn't enough. Remember that for a >> big change like that, the gain has to be big as well. > > I have done the benchmark. I have done the benchmark with Logical reads, as > they turn out to be potential physical reads. Try turning on the > log_statement_stats in postgresql.conf. try firing some queries, which can > satisfied by the index. You would see the difference.
I would see a decrease in the number of logical reads, that's all. You need to demonstrate a real increase in throughput and/or reduction in response times. Note that even though you reduce the number of logical reads, with a thick index a logical read is *more* likely to be a physical read, because the index is larger and therefore consumes more cache. > As a first test, I'd like to see results from SELECTs on different sized >> tables. On tables that fit in cache, and on tables that don't. Tables >> large enough that the index doesn't fit in cache. And as a special case, >> on a table just the right size that a normal index fits in cache, but a >> thick one doesn't. > > I have not done a Load test. That's a good idea. Are you guys using Apache > JMeter? You can use whatever you want, as long as you can get the relevant numbers out of it. contrib/pgbench is a good place to start. DBT-2 is another test people often use for patches like this. It's quite tedious to set up and operate, but it'll give you nice very graphs. Make sure you control vacuums, checkpoints etc., so that you get repeatable results. > Also i think you might have noted that the thick indexes are not affected by > updates, if the updated column is not in the index. I think that add on to > one more advantage of thick indexes against DSM. That cannot possibly work. Imagine that you have a table ctid | id | data -----+----+----- (0,1)| 1 | foo (0,2)| 1 | bar where (0,2) is an updated version of (0,1). If you don't update the index, there will be no index pointer to (0,2), so a regular index scan, not an index-only scan, will not find the updated tuple. Or did you mean that the index is not updated on HOT updates? That's an interesting observation. We could do index-only scans with the DSM as well, even if there's HOT updates, if we define the bit in the bitmap to mean "all tuples in this page are visible to everyone, or there's only HOT updates". That works, because an index-only-scan doesn't access any of the updated columns. It probably isn't worth it, though. Seems like a pretty narrow use case, and makes it more complicated. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate