custom types and optimization

[ CC'ing general list so you can see what we are working on, and my plea
for help in getting the word out about PostgreSQL's speed and features.
Replies will go the the hackers list because we don't want long
discussions like this in the general list.]

First, let me say you are thinking exactly like me. I agree 100% with
your ideas, and analysis of the issues.

I know that custom types account for a portion of overhead, and I'm
not by any means advocating their removal. I also know that the
efficiency of postgres has improved greatly since the early days, and
I'm wondering what more can be done.

Good question, and a question I have been asking myself.

For instance, would it be possible to cache results of the
input/output functions for the types? i.e. if we've already called
foobar_out for a peice of data, why call it again? We could store the
previous result in a hash, and then use that.

Not sure if that would help. We cache system tables lookups, and data
blocks. gprof does not show a huge problem in the type extensibility
area, at least in my tests.

gprof is your friend. Try compiling with the options, and run it and
analyze gmon.out (See FAQ for info.) That usually tells me quite a bit.

Note that I next to nothing about how the query node tree gets
executed (I'm reading up on it now) so this may not be possible or
could even introduce extra overhead.

Also, I hope people are reading the developers FAQ, because I think that
can help people get started with coding.

I'd like to get postgres up to speed. I know it is a great database,
and I tell all my friends this, but there is too much pg bashing
because of the early days. People think mysql rocks because it is so
fast, but in reality, well.. It's all IMHO, and the right tool for
the right job.

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

The highly-biased MySQL comparison page hurts us too, but other people
explaining real issues can counter that.

So my real question is: have we hit the limit on optimization and
reduction of overhead, or is there more work to be done? Or should we
concentrate on other aspects such as inheritance issues? I'm not
quite as interested in ANSI compliance.

Not sure. I just removed exec(), so that saves us 0.01 on startup,
which is pretty major. We can move some of the initialization that is
done in every backend to the postmaster, but these will only do major
speedups for backends that do startup, short query, exit. Longer
queries and long-running backends don't see much change.

I have tested the throughput of sequential table scan, and it appears to
run pretty quickly, almost as quick as dd on the same file. That is
pretty good. Faster than wc on my system.

So why are we considered slow? First, historically, performance has not
been a major concern, first not at Berkeley(?), and second there were so
many other problems, that we did not have the resources to concentrate
on it. Only in the past nine months have there been real improvements,
and it takes time to get the word out.

Second, it is our features that make us slower. Transactions, type
system, optimizer all add to the slowness. We are very modular, and
have a large call overhead moving in and out of modules, though
profiling has enabled us to reduce this.

MySQL also has certain limitations that allow them to be faster, like
being able to specify indexes ONLY at table creation time, so their
indexes are in with the data. They use ISAM, which doesn't grow well,
but does provide good performance because the data is kind of pre-sorted
on the disk. Our CLUSTER command now does a similar function, without
the problems of ISAM.

I am glad David Gould and others are involved, because I am starting to
run out of tricks to speed things up. I need new ideas and perhaps
redesigned modules to get better performance.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I'd like to get postgres up to speed. I know it is a great database,
and I tell all my friends this, but there is too much pg bashing
because of the early days. People think mysql rocks because it is so
fast, but in reality, well.. It's all IMHO, and the right tool for
the right job.

Yes, you're right. mysql is a lighter-weight system, without some of the
fundamental capabilities of postgres, but well suited to some
applications. Postgres is a "real database" (from David Gould :) with
more capabilities and more machine cycles needed to get those
capabilities.

So my real question is: have we hit the limit on optimization and
reduction of overhead, or is there more work to be done? Or should we
concentrate on other aspects such as inheritance issues? I'm not
quite as interested in ANSI compliance.

I think that v6.4 will have a good chunk of SQL92 compliance finished
off, and that other topics will become more actively developed in future
releases. Just guessing, but the area of postgres which has had the
fewest fundamental changes is in the backend executor. Or I should say
that the ripest place for more changes and improvements is in that area,
since I know that Vadim, Bruce, and others have been working on it for
some time.

There are some data integrity features that Vadim is planning on working
on which should/may improve performance by allowing you to trade
performance for transactional integrity. For some applications this
would allow you to burn fewer cycles on the same query, getting similar
data integrity to what mysql might provide for example.

If you're looking for areas to work on, array handling needs to be fixed
up (hint hint)...

- Tom

I am glad David Gould and others are involved, because I am starting to
run out of tricks to speed things up. I need new ideas and perhaps
redesigned modules to get better performance.

Aw shucks guys, you shouldn't have... I haven't even done anything yet.
-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software 300 Lakeside Drive Oakland, CA 94612
- A child of five could understand this! Fetch me a child of five.

On Sun, 31 May 1998, Bruce Momjian wrote:

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.

When we first took this on, we were *very* problematic. But,
since we considered it to be the best that was out there, we
persevered(sp?) with the problems and improved it overall. There are
bound to be alot that, at the beginning, just didn't want to waste time
with it, saw all the problems and left...taking their bad experience with
them.

My experience is that "bad experiences" are heard more often then
good ones.

Neil built up a 'registration page' that I'm curious as to how
many ppl are actually using it...just checked, and:

postgresql=> select count(name) from register;
count
-----
1361
(1 row)

Not bad...but I don't imagine that's a tenth of all the users, is
it?

On Sun, 31 May 1998, Bruce Momjian wrote:

and I tell all my friends this, but there is too much pg bashing
because of the early days. People think mysql rocks because it is so
fast, but in reality, well.. It's all IMHO, and the right tool for
the right job.

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

The highly-biased MySQL comparison page hurts us too, but other people
explaining real issues can counter that.

I just about had a hard time getting our system admin to install
Postgresql. All I ever heard about was MSQL is already installed. I was
under the impression that Postgresql was a more full featured SQL server
than msql. And besides that, it was what I was able to install on my home
MkLinux box, to learn. We're slowly making a switch from Microsoft SQL and
Everyware's Butler SQL server to a Linux/Postgresql combo. I've been very
pleased.

The only problem I haven't been able to fix to date is calling "Dates"
from a database and displaying them like "Sunday May 31, 1998" instead
"05-31-1998"

Currently using PHP2.x not PHP3 yet...

Kevin

--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

On Sun, 31 May 1998, The Hermit Hacker wrote:

Neil built up a 'registration page' that I'm curious as to how
many ppl are actually using it...just checked, and:

postgresql=> select count(name) from register;
count
-----
1361
(1 row)

Not bad...but I don't imagine that's a tenth of all the users, is
it?

Let me know if Camping-USA isn't one of them. I'm getting over 400 hits
per day and without PostgreSQL I'd be lucky to be getting 3 a month.
Mysql wouldn't even compile without spending three weeks upgrading every
single library that was current for FreeBSD at that time (2.0.5 or 2.1.0)
and I never did figure out how many of my kids I'd have to sell to comply
with whatever licensing msql had.

I program with Sybase for a living (among the many other things admins get
to do) and the only thing that I wish were in libpq that isn't in the
Sybase dblibraries is bind. I find it convenient to bind a program
variable to a column and not have to screw with it during the retrieval
process. BUT!! at the same time, libpq has things that dblibrary doesn't
have. psql absolutely blows isql away. I've had to write applications to
give me table definitions that psql is happy to provide. Take note,
however, since it's a pain to upgrade Sybase every time it comes out and
we're stuck with the microsoft <gag> libraries that come with our license,
our upgrade path is slow and although we acquire the upgrade quickly, we
don't necessarily perform the upgrade with the same speed!

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"I'm just not a fan of promoting stupidity!
We have elected officials for that job!" -- Rock
==========================================================================

On Sun, 31 May 1998, Bruce Momjian wrote:

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.

Ok, why do you hate Linux? I have been using it since 94 and am happier than
a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
something? I don't want to start an OS war here (there are enough of those
in other places), so please reply (if you choose to do so) privately.

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Of course, someone who knows more about this will correct me if I'm wrong,
and someone who knows less will correct me if I'm right."
--David Palmer (palmer@tybalt.caltech.edu)

On Sun, 31 May 1998, David Gould wrote:

On Sun, 31 May 1998, Bruce Momjian wrote:

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.

Ok, why do you hate Linux? I have been using it since 94 and am happier than
a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
something? I don't want to start an OS war here (there are enough of those
in other places), so please reply (if you choose to do so) privately.

No no, this wasn't meant to start a flame war...most of the
oldtimers here know of my hatred for Linux, and I've admitted often that
with Linux today, it is pretty unfounded...

I used Linux pre-94...pre-v1.0..in a business/production
environment. At that time, I was hard-core Linux advocate...it was the
greatest thing since sliced bread, but, the day I hoooked it onto the
Internet, keeping it alive more then 24hrs was a chore, and it was all in
the TCP/IP networking code...switched to *BSD and have been here ever
since...

But, that first *bad* experience tends to stick with you, no
matter how good things become over time *shrug* One becomes jaded, such
that when someone asks you what OS (or RDBMS) to use, you tend to
automatically warn against the one that you've personally had bad
experiences with...*shrug*

Again, not a flame war, and not a "you should try it now"...I
have, and even looked at running Linux for a projeect here at the office
(but I found a simpler/better solution)...

On Sun, 31 May 1998, The Hermit Hacker wrote:

On Sun, 31 May 1998, David Gould wrote:

I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.

Ok, why do you hate Linux? I have been using it since 94 and am happier than
a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
something? I don't want to start an OS war here (there are enough of those
in other places), so please reply (if you choose to do so) privately.

No no, this wasn't meant to start a flame war...most of the
oldtimers here know of my hatred for Linux, and I've admitted often that
with Linux today, it is pretty unfounded...

I used Linux pre-94...pre-v1.0..in a business/production
environment. At that time, I was hard-core Linux advocate...it was the
greatest thing since sliced bread, but, the day I hoooked it onto the
Internet, keeping it alive more then 24hrs was a chore, and it was all in
the TCP/IP networking code...switched to *BSD and have been here ever
since...

I'd gone thru similar, but a more recent set of experiences have soured
me. I've seen some out-of-the-box linux installations failing from
overload where the equivelent in a FreeBSD environment wasn't. When I'd
have 200+ users getting their email and a bunch of 'em also getting their
web pages hit, nasty things were hitting the fan. As an admin it's a
problem that eventual tuning wasn't good enuf. When you have PAYING
customers crabbing about the services they're paying for not being up
to their expectations, as a provider you have to answer to 'em. We were
able to get a couple of linux boxen going to meet the need, but our
experiences beyond that were that a FreeBSD box was able to instantly
provide a much higher level of service with a much higher level of
reliability to it for a lower cost (the cost of setting things up is,
of course, figured into the overall cost).

Do I wanna see all of the linux boxen removed? No way! Most of the
security exploits are written to run on a linux platform. I don't need
to waste any time porting an exploit to a FreeBSD machine (no matter how
easy it is) just to make sure my machines aren't vulnerable.

My personal opinion? Use the proper OS for the job at hand. I can come
up with jobs that are best suited to many operating systems. If you want
to choose an operating system that's not up to par with what you need to
do or what your PAYING customer needs? Then you need to rethink your
business strategies. For what I do, OS/2 provides me with the tools I
need. For what my wife does, 95 is her choice. For my news machines
and web servers, it has to be UN*X and currently that platform is FreeBSD.
At work, it's HP-UX. You gotta use the proper tool for the job or you're
only screwing yourself. The way you evaluate the tool is noone's
responsibility but your own. Make the proper decision and you keep your
customers; blow it and someone else gets your customers. Personally I
don't like those all nite panic sessions.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"I'm just not a fan of promoting stupidity!
We have elected officials for that job!" -- Rock
==========================================================================

Agreed -- my experiences with postgresql have usually been good
(except documentation/speed wise) and I'll go near msql (or php for
that matter) just because my experiences with them at first were truly
painful.

On Sun, 31 May 1998, at 19:38:20, The Hermit Hacker wrote:

I'm "in the process of" preparing a contrib module for this purpose.
Real Soon Now.

On Sun, 31 May 1998, at 17:45:07, Kevin Heflin wrote:

On Sun, 31 May 1998, Bruce Momjian wrote:

Yes, this has frustrated me too. Why are we not getting better mention
from people? I think we can now be classified as the 'most advanced'
free database. Can we do something about mentioning that to others? We
certainly are growing market share, but i guess I would like to see more
transfers from other databases.

I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.

When we first took this on, we were *very* problematic. But,
since we considered it to be the best that was out there, we
persevered(sp?) with the problems and improved it overall. There are
bound to be alot that, at the beginning, just didn't want to waste time
with it, saw all the problems and left...taking their bad experience with
them.

My experience is that "bad experiences" are heard more often then
good ones.

Neil built up a 'registration page' that I'm curious as to how
many ppl are actually using it...just checked, and:

postgresql=> select count(name) from register;
count
-----
1361
(1 row)

Not bad...but I don't imagine that's a tenth of all the users, is
it?

Wow, that is a big number, and the 10% is probably correct. I don't
think I am even in there.

How can we reverse the "bad publicity" and get people to start looking
at us again?

Users, we need to hear from you on this, and why you chose to use
PostgreSQL. We don't need people foaming at the mouth, but we do need
our users to give use good visibility and publicity.

[Moved to general.]

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I'm "in the process of" preparing a contrib module for this purpose.
Real Soon Now.

The only problem I haven't been able to fix to date is calling
"Dates" from a database and displaying them like "Sunday May 31,
1998" instead "05-31-1998"

Hmm. Should we have the date type pay attention to the SET DATESTYLE
command? I think it doesn't at the moment...

- Tom

At 7:12 +0300 on 1/6/98, Bruce Momjian wrote:

Users, we need to hear from you on this, and why you chose to use
PostgreSQL. We don't need people foaming at the mouth, but we do need
our users to give use good visibility and publicity.

Here is my story:

We needed to write some web-based applications, and they needed to rely on
a database, as the data stored in them needed something more complex than
ndbm.

The head of my programming team said PostgreSQL. Our sysadmin insisted on
deciding from a list of alternatives. So I set about with three main goals:
(1) ANSI compatibility (the more compatibility, the less migration pain in
case migration was needed). (2) Support for multiuser access. (3)
Interfaces to Perl and Java.

I saw the MySQL page. It seemed to be more ANSI compatible. We downloaded
it, and then it turned out that MySQL doesn't support transactions.

No transactions? That means no multiuser access. We want people to be able
to update the database. That immediately classified MySQL as "not a real
database", and put us back on the PostgreSQL route, as no other free
database was even close to the required feature list.

PostgreSQL has all the interfaces we need, it supports transactions and
locks, it is becoming more ANSI compatible with every version update, and
it seems to perform well enough.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

On Mon, 1 Jun 1998, Bruce Momjian wrote:

postgresql=> select count(name) from register;
count
-----
1361
(1 row)

Not bad...but I don't imagine that's a tenth of all the users, is
it?

Wow, that is a big number, and the 10% is probably correct. I don't
think I am even in there.

I know I'm not ;(

How can we reverse the "bad publicity" and get people to start looking
at us again?

Testimonials? Not fanatical statements...something intelligent
like what Herouth Maoz wrote? Maybe with a pointer to the project that
he/she used PostgreSQL for? Start up a 'User Comments and Projects'
page...?

How many ppl are actually using the 'Powered by' logo on their
sites? Actually, just looking at that, and am I the only one that finds
it unreadable? I can barely even see that its a 'cat'...

On Sun, 31 May 1998, David Gould wrote:

As you may have noticed, I am something of a Linux advocate. And, quite
seriously, I believe it possible that in five years not even Sun will be
shipping anything else.

That must be why Sun is currently paying for developers to port
FreeBSD over to the Sparc architecture, eh? *grin*

And, ya, I am serious...one of the developers on the FreeBSD
mailing lists is contracted to Sun MicroSystems to get a FreeBSD port off
the ground...

Thats great. Any victory for freed software is a victory for freed software!

Btw, Sun has also apparently just paid to join Linux International.

So maybe in five years Sun will be shipping either FreeBSD or Linux. ;-)

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Don't worry about people stealing your ideas. If your ideas are any
good, you'll have to ram them down people's throats." -- Howard Aiken

On Mon, 1 Jun 1998, David Gould wrote:

On Sun, 31 May 1998, David Gould wrote:

As you may have noticed, I am something of a Linux advocate. And, quite
seriously, I believe it possible that in five years not even Sun will be
shipping anything else.

That must be why Sun is currently paying for developers to port
FreeBSD over to the Sparc architecture, eh? *grin*

And, ya, I am serious...one of the developers on the FreeBSD
mailing lists is contracted to Sun MicroSystems to get a FreeBSD port off
the ground...

Thats great. Any victory for freed software is a victory for freed software!

Actually, I would rather see Unix dominate the market, period...I
don't really care whether that be Linux or FreeBSD or Solaris...they all
have their 'niches'...

I've said it before, and will continue to say it...the Linux
community has done a *great* job towards providing an alternative to
MicroSloth...they have a ways to go, but at least they are making inroads.

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

The only problem I haven't been able to fix to date is calling "Dates"
from a database and displaying them like "Sunday May 31, 1998" instead
"05-31-1998"

Currently using PHP2.x not PHP3 yet...

Kevin

try:
$mydate = '05-31-1998';
$mon = strtok($mydate, '-');
$day = strtok('-');
$year = strtok('-');
echo date('l F d, Y', mktime(0, 0, 0, $mon, $day, $year));