Обсуждение: number of rows
Does anybody here know a better way to count a table's number of rows, besides select count (*) from tablename? I mean, is there any internal variable that stores the number of rows instead of sequencially scanning the entire database?? Thanks. ------------------------------- http://www.vlinfo.com.br
Am Fre, 2003-06-13 um 11.04 schrieb Marcus Andree S. Magalhaes: > Does anybody here know a better way to count a table's number > of rows, besides select count (*) from tablename? > > I mean, is there any internal variable that stores the number of > rows instead of sequencially scanning the entire database?? Not in SQL itself, but the app could do that (PHP, CMS, whatever) bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
"Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes:
> I mean, is there any internal variable that stores the number of
> rows instead of sequencially scanning the entire database??
If a very approximate answer is good enough, you could consult
pg_class.reltuples, which is good as of the last VACUUM. There
is no on-the-fly maintenance of a row count, for a number of good
reasons that you can read about in the PG list archives.
regards, tom lane
will try to keep this as short as possible. =) 1. "select id from a_table where id not in (2,3,4)" works fine 2. "select id from a_table where id not in (select id from b_table)" works fine if the output of the "select id from b_table" looks like: id ---- 2 3 4 problem is "select id from b_table" in #2 statement has the following output: id ---- 2,3,4 how do i do execute the query in a single sql statement? thanks in advance. mel
On Wed, 2003-06-18 at 07:38, Mel Jamero wrote: > will try to keep this as short as possible. =) > > 1. "select id from a_table where id not in (2,3,4)" works fine > > 2. "select id from a_table where id not in (select id from b_table)" works > fine if the output of the "select id from b_table" looks like: > id > ---- > 2 > 3 > 4 > > problem is "select id from b_table" in #2 statement has the following > output: > id > ---- > 2,3,4 > > how do i do execute the query in a single sql statement? b_table.id has Repeating Values, which breaks the 1st Normal Form of relational DBMS design. Thus, redesign b_table so that id only has 1 value. -- +-----------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Oh, great altar of passive entertainment, bestow upon me | | thy discordant images at such speed as to render linear | | thought impossible" (Calvin, regarding TV) | +-----------------------------------------------------------
Mel Jamero wrote: >problem is "select id from b_table" in #2 statement has the following >output: >id >---- >2,3,4 > > Well, this is completely different from the first case, because for Postgres, "2,3,4" is just a string rather than a set of numbers (as in select id from b_table). Sure, you could solve your problem with stuff like - Build your query-String, then Execute this - Parse your "2,3,4" String But its better if you have the correct data in b_table, that is, only one id in a given row (this is the first step of a process called "Normalization". If you have more than one value in a given row/column pair, you will have a lot more problems than just this one. Cheers, Dani
thank you so much for the replies. they're really appreciated. =) please bear with me and read on. i wanted to find out for sure if i really won't get the desired result(s) in sql and have to create a function instead. yes it breaks the 1st normal form. we did this for some *practical* purposes supposedly. =) i would've wanted to discuss it here but i'll probably bore you to death or i'd end up starting a new discussion altogether. since it's in production and i need to do a quick patch without touching the external programs depending on this schema, i've to find a quick remedy. anyway, is there a way to convert the result of the 2nd sql statement as a string literal such that: "SELECT id FROM a_table WHERE id NOT IN (:result_of_2nd_sql_statement)" (where ":result_of_2nd_sql_statement" is a variable) would work? to be clear, because "select id from b_table" yields an output of: id ---- 2,3,4 and not what it would've if i normalized the table instead: id ---- 2 3 4 is it possible to make the resulting sql statement become "select id from a_table where id not in (2,3,4)" by just using a function inherent to sql (such as 'to_char' or 'cast' or an unknown function to me that takes in the result of an sql statement and converts it somehow)? thus, the result of the 2nd sql statement which is "2,3,4" is substituted in to my original sql statement. it's easier to do it outside the db backend (C, JAVA, PERL, TCL or whatever) but i'm really trying hard to achieve it from the backend. anyway, i've tried the ff but haven't found the solution yet: 1) assign the result in the sql statement to a variable and proceed with select #1 substituting the variable 2) "convert" the result of the sql statement "select id from b_table" into characters to apply the 1st sql statement. i'm currently creating a function that's suppose to do the trick because i'm tired out from finding quicker remedies. the idea of echoing the result of the 2nd sql statement to a file and then echoing it back to the psql command line crossed my mind but it doesn't appear to be a neat implementation if ever it would work. any other ideas? thanks again! my apologies for being too wordy. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ron Johnson Sent: Wednesday, June 18, 2003 9:19 PM To: PgSQL Novice ML Subject: Re: [NOVICE] sql question (hopefully) On Wed, 2003-06-18 at 07:38, Mel Jamero wrote: > will try to keep this as short as possible. =) > > 1. "select id from a_table where id not in (2,3,4)" works fine > > 2. "select id from a_table where id not in (select id from b_table)" works > fine if the output of the "select id from b_table" looks like: > id > ---- > 2 > 3 > 4 > > problem is "select id from b_table" in #2 statement has the following > output: > id > ---- > 2,3,4 > > how do i do execute the query in a single sql statement? b_table.id has Repeating Values, which breaks the 1st Normal Form of relational DBMS design. Thus, redesign b_table so that id only has 1 value. -- +-----------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Oh, great altar of passive entertainment, bestow upon me | | thy discordant images at such speed as to render linear | | thought impossible" (Calvin, regarding TV) | +----------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Hi Dani! I got your point about the normalization. Thanks! How do i "build the query-string" from pgsql without creating a function? Is this even possible? I've almost given up and i'm now creating the function that can "build the query-string" but if there's another way, I'd really like to find out. =) The reason why I didn't normalize was because i would've ended up with millions of tuples and the reply from the database would be too slow for the application(s) we built. I tried to come up with a better schema but I couldn't find one that really returns a fast reply so I settled with the one i presented. I did break other rules of proper Relational Analysis and Design for the sake of fast replies we needed. I'll send it to this list in the future to get better ideas. I just have to solve our current problem. Best Regards, Mel -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Dani Oderbolz Sent: Wednesday, June 18, 2003 9:28 PM To: pgsql-novice Subject: Re: [NOVICE] sql question (hopefully) Mel Jamero wrote: >problem is "select id from b_table" in #2 statement has the following >output: >id >---- >2,3,4 > > Well, this is completely different from the first case, because for Postgres, "2,3,4" is just a string rather than a set of numbers (as in select id from b_table). Sure, you could solve your problem with stuff like - Build your query-String, then Execute this - Parse your "2,3,4" String But its better if you have the correct data in b_table, that is, only one id in a given row (this is the first step of a process called "Normalization". If you have more than one value in a given row/column pair, you will have a lot more problems than just this one. Cheers, Dani ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
On Wed, Jun 18, 2003 at 22:40:49 +0800, Mel Jamero <mel@gmanmi.tv> wrote: > > The reason why I didn't normalize was because i would've ended up with > millions of tuples and the reply from the database would be too slow for the > application(s) we built. I tried to come up with a better schema but I > couldn't find one that really returns a fast reply so I settled with the one > i presented. > > I did break other rules of proper Relational Analysis and Design for the > sake of fast replies we needed. I'll send it to this list in the future to > get better ideas. I just have to solve our current problem. Did you actually test both the normalized and denormalized versions to see which is faster?
Mel, > i wanted to find out for sure if i really won't get the desired result(s) > in sql and have to create a function instead. Perhaps, but the function better be in C, or your performance will continue to suck ... > yes it breaks the 1st normal form. we did this for some *practical* > purposes supposedly. =) i would've wanted to discuss it here but i'll > probably bore you to death or i'd end up starting a new discussion > altogether. since it's in production and i need to do a quick patch > without touching the external programs depending on this schema, i've to > find a quick remedy. To be frank, boyo, you're on the road to hell. I am constantly amazed at how many newbie DBAs adopt not-normalized schema because "it's faster" and then have to add in workarounds that kill the performance of their database. Anyway, if someone else doesn't solve it, I'll come up with a SQL solution later in the week .... -- Josh Berkus Aglio Database Solutions San Francisco
Josh,
Thanks. I created a plpgsql function that works but I'll take your advice
and do it in C.
Hopefully, I'll find a way to justify the hiring of true DBA. It has always
been a *challenge* for the companies I've worked for because DBA = cost. Oh
well, maybe it's because I got the job done anyhow from their point of view.
=) Hmmm.. welcome to the Third World.
I was actually made to comply to adopt the darned schema because of the
faster reply despite the setbacks I'm (we were) well aware of. We're in
this business where we could get a million hits in a span of a few minutes
and we need to reply to these hits ASAP or we lose more potential traffic
which meant lost potential revenue. Yes, we earn from the traffic.
The SQL solution would be good stock knowledge for future reference but then
again, hopefully I wouldn't have to use it anymore. =)
With the help of the Postgres lists, the documentation that's getting better
and better, ArsDigita's and now RedHat's OpenACS (and in the future the
Postgres books I've already ordered), I've made tremendous progress from 0
knowledge in RDBMS specifically in Postgres. So, thanks to you guys and
hopefully you don't get tired of replying to the questions because they sure
help a lot.
--
Mel
p.s. I'm hoping to publish the schema here in the near future to get your
opinion on how to improve it without sacrificing too much speed.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Wednesday, June 18, 2003 11:14 PM
To: mel@gmanmi.tv; 'Ron Johnson'; 'PgSQL Novice ML'; 'Dani Oderbolz'
Subject: Re: [NOVICE] sql question (hopefully)
Mel,
> i wanted to find out for sure if i really won't get the desired result(s)
> in sql and have to create a function instead.
Perhaps, but the function better be in C, or your performance will continue
to
suck ...
> yes it breaks the 1st normal form. we did this for some *practical*
> purposes supposedly. =) i would've wanted to discuss it here but i'll
> probably bore you to death or i'd end up starting a new discussion
> altogether. since it's in production and i need to do a quick patch
> without touching the external programs depending on this schema, i've to
> find a quick remedy.
To be frank, boyo, you're on the road to hell.
I am constantly amazed at how many newbie DBAs adopt not-normalized schema
because "it's faster" and then have to add in workarounds that kill the
performance of their database.
Anyway, if someone else doesn't solve it, I'll come up with a SQL solution
later in the week ....
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Mel, > Thanks. I created a plpgsql function that works but I'll take your advice > and do it in C. Yeah. I had a few thoughts about doing it in SQL, but they all involved unanchored text searches ... with consequent super-sucky performance. > Hopefully, I'll find a way to justify the hiring of true DBA. It has always > been a *challenge* for the companies I've worked for because DBA = cost. Oh > well, maybe it's because I got the job done anyhow from their point of view. > =) Hmmm.. welcome to the Third World. Well, your company is hardly the first to act penny-wise and thousand$-foolish in terms of expensing a project; most of my $200/hour DBA consulting gigs come from companies whose database was royally screwed up for 6months- 1 year and they finally got tired of it. > I was actually made to comply to adopt the darned schema because of the > faster reply despite the setbacks I'm (we were) well aware of. We're in > this business where we could get a million hits in a span of a few minutes > and we need to reply to these hits ASAP or we lose more potential traffic > which meant lost potential revenue. Yes, we earn from the traffic. There are ways around this that don't involve denormalization of the central data tables. "Cache tables" for one, with deferred processing for permanent storage. > The SQL solution would be good stock knowledge for future reference but then > again, hopefully I wouldn't have to use it anymore. =) OK, I'll try when I can. > With the help of the Postgres lists, the documentation that's getting better > and better, ArsDigita's and now RedHat's OpenACS (and in the future the > Postgres books I've already ordered), I've made tremendous progress from 0 > knowledge in RDBMS specifically in Postgres. So, thanks to you guys and > hopefully you don't get tired of replying to the questions because they sure > help a lot. Please also check out the book review list. Particularly, Fabian Pascal's book is a must for you. http://techdocs.postgresql.org/bookreviews.php -- -Josh Berkus Aglio Database Solutions San Francisco
> http://techdocs.postgresql.org/bookreviews.php Sorry, wrong URL: http://techdocs.postgresql.org/techdocs/bookreviews.php -- -Josh Berkus Aglio Database Solutions San Francisco