[SQL] Own opclass and LIKE problem again!

[Atesz]

Hi!

Thank you very much the answers for my previous 'Multi ordered select
and indexing' question! I tried your suggestions, and those are working
well. We found a problem when used '(-col2)' instead of 'col2 DESC'.
This solution working as a functional index and in our experience when
the planner evaluates the cost of using this functional index, it uses
0.5% of the table's size. Usually this estimate is bad, and the query is
slow. Why is it working such? Preferably should I ask this on the
HACKERS or PERFORMANCE list?

But my main question how can I force the LIKE operator for using my own
operator class. I can create own LIKE operator, but it won't use my
reverse order operator class (and its indexes). How can I exchange the
standard LIKE operator with my own, which use my special reverse order
indexes?

For examle: (~~ means LIKE)
  col ~~ 'asd%'   working as  ((col >= 'asd'::text) AND (col <
'ase'::text))
I'd like to see the next:
  col /~~ 'asd%'working as  ((col />= 'asd'::text) AND (col /<
'ase'::text))

Can somebody help us?

Thanks in advance.
Antal Attila



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Join issue on a maximum value

[Atesz]

On Wed, Apr 21, 2004 at 14:29:34 -0400,
  Heflin  wrote:
> 
> SELECT auction.auction_id, image.image_id, image.image_descr FROM 
> auction JOIN image ON auction.auction_id = image.auction_id WHERE 
> auction.auction_owner = 'Mabel';
> 

In my opinion there are 2 problem: how can you make the query and how
many rows is in the result (performace)? Usually when you have more rows
in the result you can use the LIMIT and OFFSET. So you can reach the
result to unfold more pages. So I bult in these LIMIT and OFFSET into
the queries.

2 new possibilities:

  SELECT IDSEL.*, (SELECT image_descr FROM image WHERE
IDSEL.image_id=image.image_id) FROM (
SELECT auction.auction_id, max(image.image_id) AS image_id  FROM
auction JOIN image USING (auction_id)
WHERE auction_owner = 'Mabel'
GROUP BY auction.auction_id
ORDER BY auction.auction_id
LIMIT 10 OFFSET 0
  ) AS IDSEL;

or

  SELECT DISTINCT ON (image.auction_id) image.auction_id,
image.image_id, image.image_descr  FROM auction 
JOIN image USING (auction_id)
WHERE auction.auction_owner = 'Mabel'
ORDER BY image.auction_id, (-image.image_id)
  LIMIT 10 OFFSET 0;

Index suggestions: 
  CREATE INDEX auction_auction_owner on auction(auction_owner);
  CREATE INDEX image_auction_id on image(auction_id);
  CREATE INDEX image_auction_id_neg_image_id on image(auction_id,
(-image_id));  -- Specially for the second solution

The second solution build on Bruno Wolff III's ideas:
http://archives.postgresql.org/pgsql-sql/2004-04/msg00211.php and
http://archives.postgresql.org/pgsql-sql/2004-04/msg00262.php .

You can see more solutions for your problem. You have  to select the
best performance solution for your specific databse. Use the EXPLAIN!

Regards, Antal Attila




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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