Re: [SQL] OR clause causing strange index performance

[Atesz]

Hi!

I read your JOIN - Index Scaning - OR problem. I don't understand why
you decomposed JOINs two brach (ul1 and ul2).
If I understand your problem well I can suggest the next idea for your
QUERY (you don't need two branch):

SELECT *  FROM  permissions p
  INNER JOIN users u   ON u.id  = p.id
  INNER JOIN user_list ul  ON ul.id = u.id
  INNER JOIN lists l  ON ( l.list_id1 = ul.list_id1 AND
l.list_id2 = ul.list_id2 )
WHERE 
  (ul.type = '1' OR ul.type= '2') and p.code = '123456' AND p.type =
'User';

If ul.type field is integer you can optimze the OR (which can cause
index scan problem and low performance) with BETWEEN:

SELECT *  FROM  permissions p
  INNER JOIN users u   ON u.id  = p.id
  INNER JOIN user_list ul  ON ul.id = u.id
  INNER JOIN lists l  ON ( l.list_id1 = ul.list_id1 AND
l.list_id2 = ul.list_id2 )
WHERE 
  ul.type BETWEEN 1 AND 2   and p.code = '123456' AND p.type = 'User';

After that you need some good index on ul.type, p.code and p.type. You
have to think about creating indices. Analyse the results of explain!!!
In my opinion this solution may be very fast.

Regards,
Antal Attila


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Doug Y
Sent: Thursday, May 20, 2004 7:32 PM
To: [EMAIL PROTECTED]
Subject: [SQL] OR clause causing strange index performance


SELECT *
   FROM permissions p
INNER JOIN users u
ON u.id = p.id
LEFT JOIN user_list ul1
   ON ul1.id = u.id
  AND ul1.type = '1'
LEFT JOIN user_list ul2
   ON ul2.id = u.id
  AND ul2.type = '2'
INNER JOIN lists l
ON ( l.list_id1 = ul1.list_id1 AND l.list_id2 =
ul1.list_id2 )
 OR
   ( l.list_id1 = ul2.list_id1 AND l.list_id2 =
ul2.list_id2 )
  WHERE
p.code = '123456' AND p.type = 'User'




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

[SQL] About intervals

[Pablo Velasquez Rivas]

Hi all.
I have a table with some timestamps fields. For example this table:

          date1    |         date2    
-+---
 2004-03-01 11:20:00 | 2004-03-01 11:55:00

If I do a query like this, I get:

 SELECT date2 - date1 as diff FROM table;
 diff  
---
 00:35
(1 row)


 but if I do a query like this, I get:
 SELECT date2 - date1 as diff FROM table WHERE date2 - date1 >=
interval '15:00';
 diff 
--
(0 rows)

What can I do to do a query like the previous one , and get the right
answer??

Thanks !!
-- 

Firma
==
==
   Pablo A. Velasquez Rivas
Computer Science Engineer
==
==
 All programmers are optimists  -- Frederick P.
Brooks, Jr.

Re: [SQL] About intervals

[Bruno Wolff III]

On Fri, May 28, 2004 at 12:27:53 -0600,
  Pablo Velasquez Rivas <[EMAIL PROTECTED]> wrote:
> 
> but if I do a query like this, I get:
> / SELECT date2 - date1 as diff FROM table WHERE date2 - date1 >= 
> interval '15:00';/
> * diff
> --
> (0 rows)
> *
> What can I do to do a query like the previous one , and get the right 
> answer??

It looks like 15:00 is being taking to mean 15 hours 0 minutes.
The more normal syntax for specifying 15 minutes is '15 minutes.
So you probably want:
SELECT date2 - date1 as diff FROM table WHERE date2 - date1 >= interval
'15 minutes';

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster