Thread: How can I optimize a research on text field?
Hello,
What index method can I use for a text field to have a better
optimisation.
I tried btree and hash method, but when I make a clause like : where nom
like '%PICASSO%'; the request don't use any index!?!
example:
mayer98=> explain select count(*) from artistes, oeuvres where
artistes.ref=oeuvres.refartiste and nom like '%PICASSO%';
NOTICE: QUERY PLAN:
Aggregate (cost=4894.04 size=0 width=0)
-> Nested Loop (cost=4894.04 size=874 width=8)
-> Seq Scan on artistes (cost=4837.37 size=1 width=4)
-> Index Scan on oeuvres (cost=56.67 size=685510 width=4)
EXPLAIN
Jérôme Knöbl
On Fri, 30 Oct 1998, Jerome Knobl wrote:
> I tried btree and hash method, but when I make a clause like : where nom
> like '%PICASSO%'; the request don't use any index!?!
Check the FAQ, question 3.10 (or thereabouts). Text searches only use
the index if they are searching for text at the start of the field.
<mike
Can any one help me to CREATE RULE please give me an example with a simple table(s) definition and to create rule on that table Thanku , looking for ur favourable reply
G.Elangovan wrote:
>
> Can any one help me to CREATE RULE
> please give me an example with
> a simple table(s) definition and to create rule on that table
It's on my personal TODO to create a rule system
documentation for users too. The one in the programmers
manual for v6.4 is a bit technical, but you might want to
read it anyway.
Anything below addresses the v6.4 rule system!
Sample 1 - constraint follow/delete
CREATE TABLE prim (
pkey text,
comment text);
CREATE TABLE ref (
rkey text,
skey integer,
comment text);
--
-- Rule to let ref.rkey follow prim.pkey
--
CREATE RULE prim_follow AS ON UPDATE TO prim
WHERE NEW.pkey != OLD.pkey DO
UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
--
-- Rule to constraint delete references from ref to pkey
--
CREATE RULE prim_delete AS ON DELETE TO prim DO
DELETE FROM ref WHERE rkey = OLD.pkey;
--
-- Some tests
--
INSERT INTO prim VALUES ('k1', 'to get updated');
INSERT INTO prim VALUES ('k2', 'to get deleted');
INSERT INTO ref VALUES ('k1', 1, 'must follow');
INSERT INTO ref VALUES ('k1', 2, 'must follow');
INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
SELECT * FROM ref;
rkey|skey|comment
----+----+----------------
k2 | 1|must get deleted
k2 | 2|must get deleted
new1| 1|must follow
new1| 2|must follow
(4 rows)
DELETE FROM prim WHERE pkey = 'k2';
SELECT * FROM ref;
rkey|skey|comment
----+----+-----------
new1| 1|must follow
new1| 2|must follow
(2 rows)
Sample 2 - logging of table changes
CREATE TABLE emp (
ename text,
dept text,
salary money);
CREATE TABLE emp_log (
ename text,
action text,
newsal money,
uname name,
when datetime);
--
-- Rule to log new created employees
--
CREATE RULE emp_ins AS ON INSERT TO emp DO
INSERT INTO emp_log VALUES (
NEW.ename,
'employed',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when salary raises
--
CREATE RULE emp_raise AS ON UPDATE TO emp
WHERE NEW.salary > OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'raised',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when salary is lowered
--
CREATE RULE emp_lower AS ON UPDATE TO emp
WHERE NEW.salary < OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'lowered',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when employee is fired
--
CREATE RULE emp_fire AS ON DELETE TO emp DO
INSERT INTO emp_log VALUES (
OLD.ename,
'fired',
'$0',
getpgusername(),
'now'::text);
--
-- Some tests
--
INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
DELETE FROM emp WHERE ename = 'gates';
SELECT * FROM emp_log;
ename|action |newsal |uname|when
-----+--------+----------+-----+----------------------------
wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
(5 rows)
Note: The explicit 'now'::text is required. Otherwise 'now'
is interpreted at CREATE RULE time and all log entries will
contain that date instead of the time it happened.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
I tried Creating RULE with both the example
In first example both the rule failed with error "old table not found"
In case of second example
i managed to create
but other three rule failed
CREATE RULE emp_lower AS ON UPDATE TO emp
WHERE NEW.salary < OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'lowered',
NEW.salary,
getpgusername(),
'now'::text);
ERROR: old: Table does not exist.
CREATE RULE emp_fire AS ON DELETE TO emp DO
INSERT INTO emp_log VALUES (
OLD.ename,
'fired',
'$0',
getpgusername(),
'now'::text);
ERROR: old: Table does not exist.
Please giveme some more info on the KEY word (NEW and OLD).attribute
Note : iam using postgresql 6.3.2
Thanku , for ur kind help
On Wed, 4 Nov 1998, Jan Wieck wrote:
> G.Elangovan wrote:
>
> >
> > Can any one help me to CREATE RULE
> > please give me an example with
> > a simple table(s) definition and to create rule on that table
>
> It's on my personal TODO to create a rule system
> documentation for users too. The one in the programmers
> manual for v6.4 is a bit technical, but you might want to
> read it anyway.
>
> Anything below addresses the v6.4 rule system!
>
> Sample 1 - constraint follow/delete
>
> CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
> --
> -- Rule to let ref.rkey follow prim.pkey
> --
> CREATE RULE prim_follow AS ON UPDATE TO prim
> WHERE NEW.pkey != OLD.pkey DO
> UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
>
> --
> -- Rule to constraint delete references from ref to pkey
> --
> CREATE RULE prim_delete AS ON DELETE TO prim DO
> DELETE FROM ref WHERE rkey = OLD.pkey;
>
> --
> -- Some tests
> --
> INSERT INTO prim VALUES ('k1', 'to get updated');
> INSERT INTO prim VALUES ('k2', 'to get deleted');
> INSERT INTO ref VALUES ('k1', 1, 'must follow');
> INSERT INTO ref VALUES ('k1', 2, 'must follow');
> INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
> INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
>
> UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+----------------
> k2 | 1|must get deleted
> k2 | 2|must get deleted
> new1| 1|must follow
> new1| 2|must follow
> (4 rows)
>
> DELETE FROM prim WHERE pkey = 'k2';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+-----------
> new1| 1|must follow
> new1| 2|must follow
> (2 rows)
>
> Sample 2 - logging of table changes
>
> CREATE TABLE emp (
> ename text,
> dept text,
> salary money);
>
> CREATE TABLE emp_log (
> ename text,
> action text,
> newsal money,
> uname name,
> when datetime);
>
> --
> -- Rule to log new created employees
> --
> CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary raises
> --
> CREATE RULE emp_raise AS ON UPDATE TO emp
> WHERE NEW.salary > OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'raised',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary is lowered
> --
> CREATE RULE emp_lower AS ON UPDATE TO emp
> WHERE NEW.salary < OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'lowered',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when employee is fired
> --
> CREATE RULE emp_fire AS ON DELETE TO emp DO
> INSERT INTO emp_log VALUES (
> OLD.ename,
> 'fired',
> '$0',
> getpgusername(),
> 'now'::text);
>
>
> --
> -- Some tests
> --
> INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
> INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
>
> UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
> UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
>
> DELETE FROM emp WHERE ename = 'gates';
>
> SELECT * FROM emp_log;
> ename|action |newsal |uname|when
> -----+--------+----------+-----+----------------------------
> wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
> gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
> wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
> gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
> gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
> (5 rows)
>
> Note: The explicit 'now'::text is required. Otherwise 'now'
> is interpreted at CREATE RULE time and all log entries will
> contain that date instead of the time it happened.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
>
>
My first mail was bit incomplete
Note : OLD is not recognized when i used to create rule on example given
below,i tried with CURRENT instead of OLD , rule was created
but when i execute some operation on it backend connection get closed
my practical result is
CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
is Ok
CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
is also OK Created
CREATE RULE emp_del as on DELETE to emp
do DELETE from emp_log where ename=OLD.ename
old table not found
but
systemdb=> CREATE RULE emp_del as on DELETE to emp
systemdb-> do DELETE from emp_log where ename=current.ename
systemdb-> ;
CREATE
systemdb=>
systemdb=> select * from emp;
ename |dept |salary
-------+-----------+----------
wieck |development|$8,000.00
gates |management |$15,000.00
elangog|lucent |$1,000.00
(3 rows)
systemdb=> delete from emp where ename='gates';
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
This probably means the backend terminated abnormally before or
while processing the request.
systemdb=>
please help me
to create a rule on DELETE
Thanku ,expecting helpful reply
On Wed, 4 Nov 1998, Jan Wieck wrote:
> G.Elangovan wrote:
>
> >
> > Can any one help me to CREATE RULE
> > please give me an example with
> > a simple table(s) definition and to create rule on that table
>
> It's on my personal TODO to create a rule system
> documentation for users too. The one in the programmers
> manual for v6.4 is a bit technical, but you might want to
> read it anyway.
>
> Anything below addresses the v6.4 rule system!
>
> Sample 1 - constraint follow/delete
>
> CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
> --
> -- Rule to let ref.rkey follow prim.pkey
> --
> CREATE RULE prim_follow AS ON UPDATE TO prim
> WHERE NEW.pkey != OLD.pkey DO
> UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
>
> --
> -- Rule to constraint delete references from ref to pkey
> --
> CREATE RULE prim_delete AS ON DELETE TO prim DO
> DELETE FROM ref WHERE rkey = OLD.pkey;
>
> --
> -- Some tests
> --
> INSERT INTO prim VALUES ('k1', 'to get updated');
> INSERT INTO prim VALUES ('k2', 'to get deleted');
> INSERT INTO ref VALUES ('k1', 1, 'must follow');
> INSERT INTO ref VALUES ('k1', 2, 'must follow');
> INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
> INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
>
> UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+----------------
> k2 | 1|must get deleted
> k2 | 2|must get deleted
> new1| 1|must follow
> new1| 2|must follow
> (4 rows)
>
> DELETE FROM prim WHERE pkey = 'k2';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+-----------
> new1| 1|must follow
> new1| 2|must follow
> (2 rows)
>
> Sample 2 - logging of table changes
>
> CREATE TABLE emp (
> ename text,
> dept text,
> salary money);
>
> CREATE TABLE emp_log (
> ename text,
> action text,
> newsal money,
> uname name,
> when datetime);
>
> --
> -- Rule to log new created employees
> --
> CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary raises
> --
> CREATE RULE emp_raise AS ON UPDATE TO emp
> WHERE NEW.salary > OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'raised',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary is lowered
> --
> CREATE RULE emp_lower AS ON UPDATE TO emp
> WHERE NEW.salary < OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'lowered',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when employee is fired
> --
> CREATE RULE emp_fire AS ON DELETE TO emp DO
> INSERT INTO emp_log VALUES (
> OLD.ename,
> 'fired',
> '$0',
> getpgusername(),
> 'now'::text);
>
>
> --
> -- Some tests
> --
> INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
> INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
>
> UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
> UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
>
> DELETE FROM emp WHERE ename = 'gates';
>
> SELECT * FROM emp_log;
> ename|action |newsal |uname|when
> -----+--------+----------+-----+----------------------------
> wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
> gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
> wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
> gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
> gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
> (5 rows)
>
> Note: The explicit 'now'::text is required. Otherwise 'now'
> is interpreted at CREATE RULE time and all log entries will
> contain that date instead of the time it happened.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
>
>
>
>
> I tried Creating RULE with both the example
> In first example both the rule failed with error "old table not found"
> In case of second example
> i managed to create
>
> [...]
>
> ERROR: old: Table does not exist.
> Please giveme some more info on the KEY word (NEW and OLD).attribute
> Note : iam using postgresql 6.3.2
>
> Thanku , for ur kind help
DON'T TRY TO USE THE RULE SYSTEM IN A v6.3.2 DATABASE. IT'S
BUGGY AND DOES NOT WORK AT ALL.
All I wrote depends on the major changes I've done in the
rule system for v6.4.
In v6.4 CURRENT is aliased with OLD and in v6.5 CURRENT will
disappear completely.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #