From: | Waldomiro <waldomiro(at)shx(dot)com(dot)br> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Too much blocks read |
Date: | 2009-11-18 16:22:30 |
Message-ID: | 4B041F46.8080605@shx.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello everbody,
I´m doing some tests with a large table about 7 milions tuples.
So, I need to retrieve only the last value for some key. That key has
about 20.000 tuples in this table.
SELECT field1
FROM table_7milions
WHERE field1 = 'my_key'
ORDER BY field1 DESC
LIMIT 1
The statistics tables shows the postgres read about 782656 block from
disk for the table and more 24704 blocks from disk for the index.
A simple select is reading about 98 MB from disk and putting into shared
memory.
So I did some tests like that:
-- I have created a partial table for that key
SELECT *
INTO part_table
FROM table_7milions
WHERE field1 = 'my_key'
-- Now I do the same select on the same 20.000 tuples, but in the
part_table
SELECT field1
FROM part_table
WHERE field1 = 'my_key'
ORDER BY field1 desc
LIMIT 1
Now the statistics shows the postgres read 54016 blocks from disk, only
for the table becouse it doesn´t have a index.
The same select is reading about 6 MB from disk and putting into shared
memory.
I´m thinking It hapens because in the 7 millions tables, the same 8k
block has diferent records with different keys, so only a few records
with 'my_key' is retrieved when I read a 8k block.
In the part_table, all records stored in a 8k block have 'my_key', so
It´s much optimized.
My doubt, there is a way to defrag my 7 millions table to put all
records with the same key in the same 8k block?
How can I do that?
If there is not, I think it´s a good idea for the next versions.
Thank you,
Waldomiro Caraiani
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Wakeling | 2009-11-18 16:23:48 | Re: Too much blocks read |
Previous Message | Matthew Wakeling | 2009-11-18 11:39:03 | Re: Weird index or sort behaviour |