Обсуждение: Table Stats
Hi! Is there any quick way to determine how many rows exist in a given table? Thanks! --------------------------------------------- Jeff Davidson Panoptic Pictures 6888 Alta Loma Terrace Los Angeles, CA 90068 (310) 395-9300 (310) 395-9116 fax
It will only be at a given point in time, since datasbases are by design, realtime/concurrent applications. do: SELECT COUNT(*) FROM table_name; Should give you what you want. Jeff Davidson wrote: > Hi! > > Is there any quick way to determine how many rows exist in a given table? > > Thanks! > --------------------------------------------- > Jeff Davidson > Panoptic Pictures > 6888 Alta Loma Terrace > Los Angeles, CA 90068 > (310) 395-9300 > (310) 395-9116 fax > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: > It will only be at a given point in time, since datasbases are by > design, realtime/concurrent applications. > > do: > > SELECT COUNT(*) FROM table_name; > > Should give you what you want. It'll give him what he wants, but not quickly, if it's a 10/20/etc M row table. One hack is: CREATE TABLE T_CARDINALITY ( RELATION_NAME CHAR(31) PRIMARY KEY, CARDINALITY BIGINT ); Then put ON INSERT/ON DELETE triggers on each table you want to track that increment/decrement T_CARDINALITY.CARDINALITY at the appropriate moment. > Jeff Davidson wrote: > > > Hi! > > > > Is there any quick way to determine how many rows exist in a given table? -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
On 1 Aug 2003 at 9:14, Ron Johnson wrote: > On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: > > It will only be at a given point in time, since datasbases are by > > design, realtime/concurrent applications. > > > > do: > > > > SELECT COUNT(*) FROM table_name; > > > > Should give you what you want. > > It'll give him what he wants, but not quickly, if it's a 10/20/etc > M row table. > > One hack is: > CREATE TABLE T_CARDINALITY ( > RELATION_NAME CHAR(31) PRIMARY KEY, > CARDINALITY BIGINT ); How about select reltuples from pg_class where relname='mytablename'; It is as accurate as your last vacuum.. Bye Shridhar -- Strategy: A long-range plan whose merit cannot be evaluated until sometime after those creating it have left the organization.
There has been discussion of making select count(*) fast. Is this in 7.4 or will it be in 7.5? Ron Johnson wrote: > On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: > >>It will only be at a given point in time, since datasbases are by >>design, realtime/concurrent applications. >> >>do: >> >>SELECT COUNT(*) FROM table_name; >> >>Should give you what you want. > > > It'll give him what he wants, but not quickly, if it's a 10/20/etc > M row table. > > One hack is: > CREATE TABLE T_CARDINALITY ( > RELATION_NAME CHAR(31) PRIMARY KEY, > CARDINALITY BIGINT ); > > Then put ON INSERT/ON DELETE triggers on each table you want to > track that increment/decrement T_CARDINALITY.CARDINALITY at the > appropriate moment. > > >>Jeff Davidson wrote: >> >> >>>Hi! >>> >>>Is there any quick way to determine how many rows exist in a given table? > >
On Fri, 2003-08-01 at 10:21, Dennis Gearon wrote: > There has been discussion of making select count(*) fast. Is this > in 7.4 or will it be in 7.5? Internal hash tables, I believe, are used in 7.4 to speed up these kinds of aggregates: select foo, count(*) from bar where wiggle='wabble' group by foo; > Ron Johnson wrote: > > > On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: > > > >>It will only be at a given point in time, since datasbases are by > >>design, realtime/concurrent applications. > >> > >>do: > >> > >>SELECT COUNT(*) FROM table_name; > >> > >>Should give you what you want. > > > > > > It'll give him what he wants, but not quickly, if it's a 10/20/etc > > M row table. > > > > One hack is: > > CREATE TABLE T_CARDINALITY ( > > RELATION_NAME CHAR(31) PRIMARY KEY, > > CARDINALITY BIGINT ); > > > > Then put ON INSERT/ON DELETE triggers on each table you want to > > track that increment/decrement T_CARDINALITY.CARDINALITY at the > > appropriate moment. > > > > > >>Jeff Davidson wrote: > >> > >> > >>>Hi! > >>> > >>>Is there any quick way to determine how many rows exist in a given table? > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+