Skip site navigation (1) Skip section navigation (2)

Re: COUNT(*) to find records which have a certain number of dependencies ?

From: Greg Stark <gsstark(at)mit(dot)edu>
To: mailreg(at)numerixtechnology(dot)de
Cc: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: COUNT(*) to find records which have a certain number of dependencies ?
Date: 2004-09-20 18:32:17
Message-ID: 87llf4hjv2.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-patchespgsql-sql
T E Schmitz <mailreg(at)numerixtechnology(dot)de> writes:

> I want to select only those BRAND/MODEL combinations, where the MODEL has more
> than one TYPE, but only where one of those has TYPE_NAME='xyz'.
> I am not interested in MODELs with multiple TYPEs where none of them are called
> 'xyz'.


There are lots of approaches to this with various pros and cons.

The simplest one off the top of my head:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
 where exists (select 1 from type where model_fk = model_pk and type_name = 'xyz')
   and (select count(*) from type where model_fk = model_pk) > 1


You could try to be clever about avoiding the redundant access to the type table:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
 where (select count(*)
          from type 
         where model_fk = model_pk 
        having sum(case when type = 'xyz' then 1 else 0 end) >= 1
       ) > 1

I'm haven't tested that, it might need some tweaking. In any case I don't
think it's worth the added complexity, assuming you have indexes on type. I'm
not even sure it would run faster.

You could try to be really clever about it by turning the whole thing into a
join:

select * 
  from brand 
  join model on (brand_pk = brand_fk)
  join (select model_fk
          from type 
         group by model_fk
        having sum(case when type = 'xyz' then 1 else 0 end) >= 1
           and count(*) > 1
       ) on (model_fk = model_pk)

This would let the planner have a more plans to choose from and might be a big
win if there are lots of brands and models but few that satisfy the criteria
you're looking for.

-- 
greg


In response to

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2004-09-20 18:52:33
Subject: Re: Fix for VACUUM in psql autocommit off
Previous:From: Tom LaneDate: 2004-09-20 17:54:45
Subject: Re: small doc patch for postgresql.conf.sample

pgsql-sql by date

Next:From: Greg StarkDate: 2004-09-20 18:33:33
Subject: Re: COUNT(*) to find records which have a certain number of
Previous:From: T E SchmitzDate: 2004-09-20 17:59:39
Subject: Re: COUNT(*) to find records which have a certain number of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group