Re: [PERFORM] TEXT or LONGTEXT?

[Niklas Johansson]

On 24 sep 2007, at 17.21, Fabiola Fernández wrote:
I am trying to decide if it would be worth using LONGTEXT instead  
of TEXT, as maybe it would slow down the data insertion and  
extraction.


Postgres doesn't have a LONGTEXT datatype, so keep using TEXT.

http://www.postgresql.org/docs/8.2/interactive/datatype-character.html



Sincerely,

Niklas Johansson




---(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

Re: [PERFORM] How to speed up this translation query?

[Niklas Johansson]

On 1 aug 2006, at 20.09, tlm wrote:

SELECT q3.translation, q2.otherstuff
FROM
(
  SELECT INPUT.word, q1.meaning_id, INPUT.otherstuff
  FROM
  INPUT
  INNER JOIN
  (
SELECT translation, meaning_id
FROM TRANS
WHERE translation IN (SELECT word FROM INPUT)
  ) AS q1
  ON INPUT.word = q1.translation
) AS q2
LEFT JOIN
(
  SELECT translation, meaning_id
  FROM TRANS
  WHERE language_id=5
) AS q3
ON q2.meaning_id=q3.meaning_id;


Maybe I'm not following you properly, but I think you've made things  
a little bit more complicated than they need to be. The nested sub- 
selects look a little nasty.


Now, you didn't provide any explain output but I think the following  
SQL will achieve the same result, and hopefully produce a better plan:


SELECT t2.translation, i.otherstuff
FROM input i INNER JOIN trans t ON i.word=t.translation
INNER JOIN trans t2 ON t.meaning_id=t2.meaning_id
WHERE t2.language_id=5;

The query will also benefit from indices on trans.meaning_id and  
trans.language_id. Also make sure the tables are vacuumed and  
analyzed, to allow the planner to make good estimates.




Sincerely,

Niklas Johansson





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

Re: [PERFORM] Trigger vs Rule

[Niklas Johansson]

On 2 apr 2006, at 23.08, Niklas Johansson wrote:


CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
EXECUTE SELECT device_type || OLD.type || (OLD.id);
$$ LANGUAGE plpgsql;



Sorry, I was bitten by the bedbug there: a plpgsql function needs a  
little more than that to be functional :)


CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
BEGIN
EXECUTE 'SELECT device_type' || OLD.type || '(OLD.id)';
RETURN NEW/OLD/NULL; -- Depending on your application.
END;
$$ LANGUAGE plpgsql;

But really, you should consider reworking your schema structure.  
Having a thousand functions doing almost the same thing is neither  
efficient, nor maintainable.




Sincerely,

Niklas Johansson





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Trigger vs Rule

[Niklas Johansson]

On 2 apr 2006, at 10.31, Ключников А.С. wrote:

What is faster?
One trigger with 1000 ELSE IF
Or 1000 rules


Faster to write and easier to maintain would be to write a trigger  
function in pl/pgsql which executes the right function dynamically:


CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
EXECUTE SELECT device_type || OLD.type || (OLD.id);
$$ LANGUAGE plpgsql;

Best would probably be to refactor your device_typeN() functions into  
one, that would take N as an argument.



Sincerely,

Niklas Johansson





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly