pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Leon <leon@udmnet.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: [SQL] bad select performance fixed by forbidding hash joins
Date: Wed, 21 Jul 1999 23:40:44 +0500
Message-ID: <3796142C.B577425D@udmnet.ru> (raw)
References: <2107.932577609@sss.pgh.pa.us>

Tom Lane wrote:
> 
> The right long-term solution is to figure out why the system is
> misestimating the relative costs of the two plans, and fix the cost
> estimates.  (The system is estimating that the mergejoin is about 4x
> slower than hash; if it's really 8x faster, there is something pretty
> broken about the estimate...)
> 


I am pleased that someone is taking seriously the speed of joins problem,
and here is a word or two of mine to say. 

The main feature of SQL as a language is it's generality. It doesn't
tell how to make things. It only expresses the result desired. This is
a strong side, because programmer doesn't have to tell the procedure,
and it's at the same time the weakness. This is weakness because 
the problem of deciding on a way is handed over to the server, since
there seems theoretically no way to estimate the cost of every query
without doing that query. It is proven very easily. Suppose you
have a join with qualification 'where a = 10'. Having statistics
of how many there are rows with a == 10 you can tell the right way.
Then suppose you have join with qualification 'table1.a = table2.b'.
This is much much more difficult to be estimated in the right way,
although still possible. If you have qualification of the kind
'SOME_WEIRD_FUNCTION(table1.a) = ANOTHER_WEIRD_FUNCTION(table2.b)' 
it becomes impossible to tell the right way.

There is a fundamental flaw in SQL which makes it difficult
to realize in practice with high efficiency. The only way to
solve the problem generally is to commit a sin against purism
and start giving hints to server.

I don't state that there is no way of improving concrete Postgres
optimizer. Definitely there is. As far as I can see, now it's
optimizer is too pessimistic about sizes of the results of joins,
it overestimates them badly. If you make it think that it is 
going to get fewer rows from a query, it will behave better. Maybe
you should even get a state variable, setting of which could
regulate it's degree of optimism in joins. But all that is quick
and dirty hack. General way is hints, however.

-- 
Leon.
---------
"This may seem a bit weird, but that's okay, because it is weird." -
Perl manpage.




view thread (7+ messages)  latest in thread

Message-ID: <3796142C.B577425D@udmnet.ru>
Permalink:  ../3796142C.B577425D@udmnet.ru/
Also on:    postgresql.org/message-id/3796142C.B577425D@udmnet.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: leon@udmnet.ru, tgl@sss.pgh.pa.us, pgsql-general@postgresql.org
  Subject: Re: [GENERAL] Re: [SQL] bad select performance fixed by forbidding hash joins
  In-Reply-To: <3796142C.B577425D@udmnet.ru>
  References: <2107.932577609@sss.pgh.pa.us> <3796142C.B577425D@udmnet.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox