Re: Critical performance problems on large databases

On Thu, Apr 11, 2002 at 11:09:05AM -0500, Gunther Schadow wrote:
[snip]
> The constructive responses suggested that I use LIMIT/OFFSET and
> CURSORs. I can see how that could be a workaround the problem, but
> I still believe that something is wrong with the PostgreSQL query
> executer.

<editorialize emphasis="mine" text="yours">

> LOADING THE ENTIRE RESULT SET INTO A BUFFER WITHOUT
> NEED JUST MAKES NO SENSE.

</editorialize>

when YOU say "SELECT * FROM SOMETABLE" that's what YOU are doing.
the software tries to accomodate your request as best it can.

> Good data base engines try to provide
> for parallel execution of the query plan as much as possible, and
> that implies streaming. There's a crowd of literature about this
> testifying for it's importance.

if you need the first twenty or the last fifty rows for your
purpose, then use limit & offset, or try the cursor approach. if
you NEED all the rows (a very unusual occurrence outside of
mirroring instances -- even periodic summation, for report
generation, will use aggregation instead of all individual rows)
then ask for them.

--
I use Debian/GNU Linux version 2.2;
Linux server 2.2.17 #1 Sun Jun 25 09:24:41 EST 2000 i586 unknown

DEBIAN NEWBIE TIP #63 from Will Trillich <will(at)serensoft(dot)com>
:
What's the best way to GET RESPONSES ON DEBIAN-USER? There are
several things to keep in mind:
1) Debians are all volunteers because they enjoy what they
do; they don't owe you diddly (and you'll be one of us
when you start getting involved): ASK, and ye shall
recieve; DEMAND, and ye shall be rebuffed
2) Provide evidence showing that you did put effort into
finding a solution to your problem (at least demonstrate
that you've seen the manual)
3) Be known to offer pointers and assistance to others
4) Give enough information so that someone else can figure
out what you're after; and make it legible
5) Enjoy yourself and have fun -- it'll come across, and we
enjoy people who enjoy life; a petulant whiner seldom
gets any useful pointers other than "Out, damn spot!"

Also see http://newbieDoc.sourceForge.net/ ...