pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How can I optimize a research on text field?
3+ messages / 3 participants
[nested] [flat]

* How can I optimize a research on text field?
@ 1998-10-30 15:38 Jerome Knobl <jknobl@mandanet.ch>
  1998-10-30 18:20 ` Re: [SQL] How can I optimize a research on text field? Mike Meyer <mwm@phone.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Jerome Knobl @ 1998-10-30 15:38 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] How can I optimize a research on text field?
  1998-10-30 15:38 How can I optimize a research on text field? Jerome Knobl <jknobl@mandanet.ch>
@ 1998-10-30 18:20 ` Mike Meyer <mwm@phone.net>
  1998-11-04 14:29   ` Re: [SQL] How can I optimize a research on text field? G.Elangovan  <elangog@wipinfo.soft.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Mike Meyer @ 1998-10-30 18:20 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] How can I optimize a research on text field?
  1998-10-30 15:38 How can I optimize a research on text field? Jerome Knobl <jknobl@mandanet.ch>
  1998-10-30 18:20 ` Re: [SQL] How can I optimize a research on text field? Mike Meyer <mwm@phone.net>
@ 1998-11-04 14:29   ` G.Elangovan  <elangog@wipinfo.soft.net>
  0 siblings, 0 replies; 3+ messages in thread

From: G.Elangovan @ 1998-11-04 14:29 UTC (permalink / raw)
  To: Mike Meyer <mwm@phone.net>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1998-11-04 14:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-30 15:38 How can I optimize a research on text field? Jerome Knobl <jknobl@mandanet.ch>
1998-10-30 18:20 ` Mike Meyer <mwm@phone.net>
1998-11-04 14:29   ` G.Elangovan  <elangog@wipinfo.soft.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox