Re: [PERFORM] Too much blocks read
In response to Waldomiro : > 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? CLUSTER your table: http://www.postgresql.org/docs/current/static/sql-cluster.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Too much blocks read
Waldomiro wrote: ... 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? Read about the "CLUSTER ON index-name" SQL command. It does exactly what you're asking. Craig -- 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] Too much blocks read
On Wed, 18 Nov 2009, Waldomiro wrote: 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 What's the point of this query? You are forcing Postgresql to read in all the rows where field1 = 'my_key', so that they can be sorted, but the sort will be completely unpredictable because all the values will be the same. If you wanted to grab any row, then remove the ORDER BY, and it will just return the first one it finds. Matthew -- The best way to accelerate a Microsoft product is at 9.8 metres per second per second. - Anonymous -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
