Re: Created Index is not used

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Markus,

first of all thanks for your quick reply!

Markus Schaber wrote:
Kjeld Peters wrote:
Select and update statements are quite slow on a large table with more
than 600,000 rows. The table consists of 11 columns (nothing special).
The column "id" (int8) is primary key and has a btree index on it.

The following select statement takes nearly 500ms:

SELECT * FROM table WHERE id = 600000;


Known issue which is fixed in 8.X servers, postgreSQL sees your 600000
as int4 literal and does not grasp that the int8 index works for it.

SELECT * FROM table WHERE id = 600000::int8;

should do it.

After I appended "::int8" to the query, selecting the table takes only 40-50ms. That's a great performance boost!

Btw, you should update to 7.4.12, there are importand bug fixes and it
is upgradable "in place", without dumping and reloading the database.

I guess I'll test an upgrade to version 8.1.

Thanks again for your and Ragnar's help!

Kjeld


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux