pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance of like queries
4+ messages / 4 participants
[nested] [flat]
* performance of like queries
@ 2007-10-02 17:31 Kevin Kempter <kevin@kevinkempterllc.com>
2007-10-02 21:06 ` Re: performance of like queries Joshua D. Drake <jd@commandprompt.com>
2007-10-02 21:49 ` Re: performance of like queries Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-02 21:55 ` Re: performance of like queries Chris Browne <cbbrowne@acm.org>
0 siblings, 3 replies; 4+ messages in thread
From: Kevin Kempter @ 2007-10-02 17:31 UTC (permalink / raw)
To: pgsql-performance
Hi List;
any suggestions for improving "LIKE '%text%'" queries?
Thanks in advance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance of like queries
2007-10-02 17:31 performance of like queries Kevin Kempter <kevin@kevinkempterllc.com>
@ 2007-10-02 21:06 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joshua D. Drake @ 2007-10-02 21:06 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Kevin Kempter wrote:
> Hi List;
>
> any suggestions for improving "LIKE '%text%'" queries?
faster disks :)
take a look at pg_tgrm and tsearch2.
Sincerely,
Joshua D. Drake
>
>
> Thanks in advance
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHArLMATb/zqfZUUQRAiaSAJ4lbVKrKEgr9OnO6jDguALtnonm7QCggtsx
W7dsy40KbvizyYBQYpvsIvw=
=2J2G
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance of like queries
2007-10-02 17:31 performance of like queries Kevin Kempter <kevin@kevinkempterllc.com>
@ 2007-10-02 21:49 ` Marcin Stępnicki <mstepnicki@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Marcin Stępnicki @ 2007-10-02 21:49 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; pgsql-performance
On 10/2/07, Kevin Kempter <kevin@kevinkempterllc.com> wrote:
> Hi List;
>
> any suggestions for improving "LIKE '%text%'" queries?
http://www.depesz.com/index.php/2007/09/15/speeding-up-like-xxx/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance of like queries
2007-10-02 17:31 performance of like queries Kevin Kempter <kevin@kevinkempterllc.com>
@ 2007-10-02 21:55 ` Chris Browne <cbbrowne@acm.org>
2 siblings, 0 replies; 4+ messages in thread
From: Chris Browne @ 2007-10-02 21:55 UTC (permalink / raw)
To: pgsql-performance
kevin@kevinkempterllc.com (Kevin Kempter) writes:
> any suggestions for improving "LIKE '%text%'" queries?
If you know that the 'text' portion of that query won't change, then
you might create a partial index on the boolean condition.
That is,
create index index_foo_text on my_table (tfield) where (tfield like '%text%');
I somehow doubt that is the case; more likely you want to be able to
search for:
select * from my_table where tfield like '%this%';
select * from my_table where tfield like '%that%';
select * from my_table where tfield like '%the other thing%';
There are basically three choices, at that point:
1. Get more memory, and hope that you can have all the data get
cached in memory.
2. Get more better disk, so that you can scan the table faster on
disk.
3. Look into tsearch2, which provides a full text search capability.
--
(format nil "~S@~S" "cbbrowne" "linuxdatabases.info")
http://cbbrowne.com/info/x.html
"We're born with a number of powerful instincts, which are found
across all cultures. Chief amongst these are a dislike of snakes, a
fear of falling, and a hatred of popup windows" -- Vlatko Juric-Kokic
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-10-02 21:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-02 17:31 performance of like queries Kevin Kempter <kevin@kevinkempterllc.com>
2007-10-02 21:06 ` Joshua D. Drake <jd@commandprompt.com>
2007-10-02 21:49 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-02 21:55 ` Chris Browne <cbbrowne@acm.org>
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