[SQL] omitting redundant join predicate

[Ehab Galal]

I tried the following query 

 

explain select * 

from t1, t2, t3 

where t1.f <= t2.f

  and t2.f <= t3.f

  and t1.f <= t3.f;

 

And that's what I got:

 

Nested Loop  (cost=0.00..3.15 rows=1 width=368)

   Join Filter:
(("outer".f <= "inner".f) AND ("inner".f <=
"outer".f))

   ->  Nested Loop  (cost=0.00..2.10 rows=1 width=218)

 Join Filter:
("outer".f <= "inner".f)

 ->  Seq Scan on t1  (cost=0.00..1.01 rows=1 width=146)

 ->  Seq Scan on t3  (cost=0.00..1.04 rows=4 width=72)

   ->  Seq Scan on t2  (cost=0.00..1.02 rows=2 width=150)

 

 

I was wondering if there is a
way to omit the redundant join predicate.

 

Thanks,

--h


_
Windows Live Hotmail and Microsoft Office Outlook – together at last.  Get it 
now.
http://office.microsoft.com/en-us/outlook/HA102225181033.aspx?pid=CL100626971033

Re: [SQL] omitting redundant join predicate

[Tom Lane]

Ehab Galal <[EMAIL PROTECTED]> writes:
> explain select * 
> from t1, t2, t3 
> where t1.f <= t2.f
>   and t2.f <= t3.f
>   and t1.f <= t3.f;

> I was wondering if there is a
> way to omit the redundant join predicate.

You're not being very clear here.  Do you mean will you get the same
answer if you omit "t1.f <= t3.f"?  Yes, of course (ignoring possibly
different output ordering).  Do you mean you think the system should
discard it as redundant?  I disagree --- the more join clauses the
better, as a rule.  Do you mean that the EXPLAIN output looks like
the same comparison is being applied twice?  It isn't --- in a more
modern PG release the output looks like this:

QUERY PLAN
--
 Nested Loop  (cost=33.54..81794021.44 rows=362975624 width=12)
   Join Filter: ((t1.f <= t2.f) AND (t2.f <= t3.f))
   ->  Nested Loop  (cost=0.00..124472.40 rows=1526533 width=8)
 Join Filter: (t1.f <= t3.f)
 ->  Seq Scan on t1  (cost=0.00..31.40 rows=2140 width=4)
 ->  Seq Scan on t3  (cost=0.00..31.40 rows=2140 width=4)
   ->  Materialize  (cost=33.54..54.94 rows=2140 width=4)
 ->  Seq Scan on t2  (cost=0.00..31.40 rows=2140 width=4)
(8 rows)

This is of course the stupidest possible join plan, but it's hard to do
much better --- both hash and merge joins work only on equality
conditions.  You can do a bit better with an index on t2.f:

  QUERY PLAN  
--
 Nested Loop  (cost=0.00..1330.60 rows=362975624 width=12)
   ->  Nested Loop  (cost=0.00..124472.40 rows=1526533 width=8)
 Join Filter: (t1.f <= t3.f)
 ->  Seq Scan on t1  (cost=0.00..31.40 rows=2140 width=4)
 ->  Seq Scan on t3  (cost=0.00..31.40 rows=2140 width=4)
   ->  Index Scan using t2i on t2  (cost=0.00..5.01 rows=238 width=4)
 Index Cond: ((t1.f <= t2.f) AND (t2.f <= t3.f))
(7 rows)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] SELECT DISTINCT ... ORDER BY UPPER(column_name) fails

[Aymeric Levaux]

On pgsql 8.2, if you do the following query, everything is fine:

SELECT DISTINCT tag FROM logs ORDER BY tag;

But if you'd like to do a case insensitive order by like this:

SELECT DISTINCT tag FROM logs ORDER BY UPPER(tag);

or

SELECT DISTINCT tag FROM logs ORDER BY LOWER(tag);

You get the following error message :
for SELECT DISTINCT, ORDER BY expressions must appear in select list.

It is weird as the order by column appears in the select.

Any thoughts?

--
Aymeric Levaux

Re: [SQL] SELECT DISTINCT ... ORDER BY UPPER(column_name) fails

[Tom Lane]

Aymeric Levaux <[EMAIL PROTECTED]> writes:
> SELECT DISTINCT tag FROM logs ORDER BY UPPER(tag);
> You get the following error message :
> for SELECT DISTINCT, ORDER BY expressions must appear in select list.

> It is weird as the order by column appears in the select.

No it doesn't.  The ORDER BY expression is "UPPER(tag)" not just "tag".

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[SQL] Partitioning and Foreign Keys

[Volkan YAZICI]

Hi,

We have a customers database growing ~1,000 rows per day. (Nearly, %40
of the table is garbage and won't get queried that often in the
future. But we keep them for statistical analysing purposes of
previous jobs.) Despite table allocates relatively small size on the
disk, requirement of instantaneous query execution times forms our
major bottleneck. I planned to partition this table, but I learnt that
PostgreSQL doesn't allow referencing views. Does anybody have any
suggestions?


Regards.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Returning the total number of rows as a separate column when using limit

[Andreas Joseph Krogh]

This is a re-post as I didn't get any response last time

Hi.

AFAICS the information about the *total* number of rows is in the "result" 
somehow. When I execute a "limit 1" query with EXPLAIN ANALYZE, I se the 
total number of columns in "rows=200819", so the information is there.

andreak=# EXPLAIN ANALYZE select p.id from onp_crm_person p order by p.created 
DESC limit 1;

QUERY PLAN
---
 Limit  (cost=0.00..0.04 rows=1 width=12) (actual time=0.046..0.048 rows=1 
loops=1)
   ->  Index Scan Backward using origo_person_created_idx on onp_crm_person p  
(cost=0.00..8396.45 rows=200819 width=12) (actual time=0.041..0.041 rows=1 
loops=1)
 Total runtime: 0.104 ms
(3 rows)

Is it possible to use some sort of "magic" function to get this number out as 
a separate column? And is this number accurate? It has to be the same as 
running a separate "count(*)"-query to count the totals, which is exactly 
what I'm trying to avoid.

Oracle has a special rownum and over() which can be used to accomplish this:

SELECT tmp.*, max(rownum) over() as total_count
 FROM (subquery) tmp

Does PG have any equivalent way?

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match