Re: Advise with a select statement

On Thu, Apr 21, 2011 at 11:20 PM, Sachin Srivastava <
sachin(dot)srivastava(at)enterprisedb(dot)com> wrote:

> A naive and simple solution is: (Let the table name is table1)
> SELECT a.Team, a.Score FROM table1 a, table1 b WHERE a.Score = b. Score and
> a.Team != b.Team;
>
> On Apr 21, 2011, at 10:35 PM, JORGE MALDONADO wrote:
>
> If I have a table that has a column where values can be the same, how can I
> SELECT them?
> For example, consider the following table:
>
> --------------------------------------
> Team Score
> --------------------------------------
> T1 500
> T2 510
> T3 505
> T4 499
> T5 500
> T6 508
> T7 505
> T8 498
> T9 501
>
> As you can see, T1 and T5 have a score of 500; and T3 and T7 have a score
> of 505. What I want is a SELECT to get only these four records.
>
> Respectfully,
> Jorge Maldonado
>
>
> --
> Regards,
> Sachin Srivastava
> EnterpriseDB <http://www.enterprisedb.com>, the Enterprise PostgreSQL<http://www.enterprisedb.com>
> company.
>
>
Although the above suggested solution is good in current context, you should
also consider if there are any indexes on either of columns. If there are
any indexes, then the inequality predicate might not allow usage of indexes.
(roughly inequality is not used by index scan whereas equality, range
predicates are good for index scans)

So somehow the inequality condition has to be converted to range or similar
predicates that will benefit indexes usage.
typically you can use NOT IN, NOT EXISTS or even UNION using < and > , while
keeping the same logic.

Continuing with above query, we can make use of UNION with range predicates,
which might choose index scan depending on data/statistics, as:
SELECT a.Team, a.Score FROM table1 a, table1 b WHERE a.Score = b. Score and
a.Team > b.Team
UNION
SELECT c.Team, d.Score FROM table1 c, table1 d WHERE c.Score = d. Score and
c.Team < d.Team;

The logic of predicates remains same, but we allow optimizer to choose index
path, provided if indexes were available.
But with the previously suggested query, the optimizer with its limitations,
would not choose index path.

Likewise, you may make use of other logical operators or operations.

There are quite s few discussion titles "slowness of query", "why indexes
are not used" on the the performance d-list.
you might want to go through them for better understanding of this scenario.

--
Regards,
Chetan Suttraway
EnterpriseDB <http://www.enterprisedb.com/>, The Enterprise
PostgreSQL<http://www.enterprisedb.com/>
company.