Thread: number of rows

number of rows

From
"Marcus Andree S. Magalhaes"
Date:

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

Re: number of rows

From
Nabil Sayegh
Date:
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


Re: number of rows

From
Tom Lane
Date:
"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

sql question (hopefully)

From
"Mel Jamero"
Date:
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



Re: sql question (hopefully)

From
Ron Johnson
Date:
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)               |
+-----------------------------------------------------------


Re: sql question (hopefully)

From
Dani Oderbolz
Date:
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




Re: sql question (hopefully)

From
"Mel Jamero"
Date:
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


Re: sql question (hopefully)

From
"Mel Jamero"
Date:
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


Re: sql question (hopefully)

From
Bruno Wolff III
Date:
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?

Re: sql question (hopefully)

From
Josh Berkus
Date:
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

Re: sql question (hopefully)

From
"Mel Jamero"
Date:
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


Re: sql question (hopefully)

From
Josh Berkus
Date:
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


Re: sql question (hopefully)

From
Josh Berkus
Date:
> http://techdocs.postgresql.org/bookreviews.php

Sorry, wrong URL:
http://techdocs.postgresql.org/techdocs/bookreviews.php

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco