I believe running count(*) means fulltable scan, and there's no way to do it without it. But what about some "intermediate" table, with the necessary counts?
That means to create a table with values (counts) you need, and on every insert/delete/update increment or decrement the appropriate values. This way you won't need the count(*) query anymore, and the performance should be much better. t.v. > Salve. > I understand from various web searches and so on that PostgreSQL's MVCC > mechanism makes it very hard to use indices or table metadata to optimise > count(*). Is there a better way to guess the "approximate size" of a table? > I'm trying to write a trigger that fires on insert and performs some > maintenance (collapsing overlapping boxes into a single large box, > specifically) as the table grows. My initial attempt involved count(*) and, > as the number of pages in the table grew, that trigger bogged down the > database. > Any thoughts? ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match