Jayadevan M <maymala.jayadevan@gmail.com> wrote:
> Since the number of conditions and the type of conditions (AND,
> OR ) etc are not known beforehand, I felt that generating the
> query as above (aliasing) may be a bit more difficult compared
> to generating a list of similar INTERSECTs or UNIONs. Since we
> have equijoins and and an exact match on skill, it should get
> executed fast?
The alternative I showed should be faster, but it might not be by
enough to matter. Populating a database with enough data to match
what you expect in production and running some benchmarks is the
best way to find out.
> I got another way of doing this also.....
> WHERE SKILL IN ('JAVA','ORACLE') group by student_id having
> count(*) = 2. If there are 3 skills, do a count(*)=3.
I considered that before posting my suggestion. It should work.
> By the way, combination of student_id/skill will be unique in
> that table. Any other ideas?
It probably will generate a plan very much like my earlier
suggestion, especially since the combination is unique, but you
could add EXISTS tests to the WHERE clause instead of having the
JOINs to the skill table.
> I am collecting options - will check performance and use the
> best.
Good plan. :-) But make sure you have row sizes, row counts,
distributions, and correlations roughly comparable to what you
expect in production when you compare performance. And be sure you
have tuned the system. If any query is not performing as well as
you expected, read this and post to the pgsql-performance list:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
