Re: Huge Performance Difference on Similar Query in Pg7.2

On Fri, 22 Mar 2002, Luis Alberto Amigo Navarro wrote:

> > Aggregate  (cost=5975.66..5975.66 rows=1 width=131) (actual
> time=1264.12..1264.13 rows=1 loops=1)
> >   ->  Hash Join  (cost=5958.18..5975.58 rows=35 width=131) (actual
> time=1205.90..1262.46 rows=1606 loops=1)
> >         ->  Hash Join  (cost=5956.30..5972.99 rows=35 width=127) (actual
> time=1197.85..1248.93 rows=1606 loops=1)
>  2)
> > emep=> explain analyze select  count(*) from  EmissionsView ,
> DataSetsView where  DataSetsView.setid = EmissionsView.setid and
> DataSetsView.setid = '4614' ;
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=91660.36..91660.36 rows=1 width=131) (actual
> time=64414.80..64414.80 rows=1 loops=1)
> >   ->  Hash Join  (cost=2732.23..91660.35 rows=3 width=131) (actual
> time=58428.47..64413.14 rows=1606 loops=1)
> >         ->  Nested Loop  (cost=2719.53..91058.61 rows=117798 width=103)
> (actual time=49523.50..63005.67 rows=1025405 loops=1)
>
> I think here is the difference, in the first case you are performing
> hash-join from 33000 rows, in second case it is from 1M rows, so it is
> wasting time on hashing, it seems that 1st conditions are more restrictive
> than the second one. I mean there are less 4614 on EmissionsView than on
> DataSetsView
> Regards
>

If I may be so bold as to join this thread as well, this is a close enough
match to the subject of my long pending post I mentioned in another thread to
warrent it I believe.

I composed my message within a newsgroup thread but I had trouble posting it
there. Therefore I have some message fragments quoted at the start which will
not have been seen on this list.

To summarise: it's long (sorry), it's mostly to do with the planner not
using an index and it's long.

Here goes...