Thread: FTS don't work
Hi, all! There is one full text search solution contributed with postgres source (I use 7.1.3). And there is sed that select id from fti where string ~ '^substring' will use indexes. But it don't. As I find out index works only if I search like SELECT id from fti where string = 'string'; How can I search for strings like 'strin%' and use index? Mark
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello: I've been writing a billing system with C++, ODBC, and PostgreSQL 7.1.2. After I optimized my queries, I was surprised at how fast PostgreSQL can swoop through 200,000 billing records. But I noticed, sometimes, the final stage of my processing never completes, and PostgreSQL is spiralling the CPU at almost 100%. I have to kill the process, which is thankfully detected by my application which does a rollback. Anyway, I'm quite sure it isn't my software that is causing this, because if I do a simple vacuum after the rollback, and then run the same sequence again, the final stage completes as quickly as I've come to expect. I'm just wondering if there is a known race condition or something else which would cause this phenomenon. Thanks in advance. - ---------------< LINUX: The choice of a GNU generation. >------------- Steve Frampton <frampton@LinuxNinja.com> http://www.LinuxNinja.com GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details) GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.0 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7uvUJmnLx9dBV68URAoz5AJ4jIyNXAM1ly4iehXKczXEEtd1FNwCeLVEY SKJ4PPoMK7w2WlfmoWj+rnk= =fVJo -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wed, 3 Oct 2001, Steve Frampton wrote: > But I noticed, sometimes, the final stage of my processing never > completes, and PostgreSQL is spiralling the CPU at almost 100%. I > have to kill the process, which is thankfully detected by my > application which does a rollback. > > Anyway, I'm quite sure it isn't my software that is causing this, because > if I do a simple vacuum after the rollback, and then run the same sequence > again, the final stage completes as quickly as I've come to expect. Just thought I'd add that, when my process runs normally, PostgreSQL takes between 30% and 50% of CPU. Only when this mysterious condition occurs does it hit near 100%. Also, I happened to notice upon vacuuming the DB the last time this happened, slightly larger than a multiple of my tuples were deleted from the index -- which seems to indicate that my application did the processing it was supposed to have done, but somehow PostgreSQL got "stuck" on the final update. Strange. - ---------------< LINUX: The choice of a GNU generation. >------------- Steve Frampton <frampton@LinuxNinja.com> http://www.LinuxNinja.com GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details) GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.0 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7uvlhmnLx9dBV68URAvBQAJ9oeMWMpKmw0br/ISXrlKgZLe80xQCggg5Y R8RPG9jyVuzo2TV3qoLquLY= =3st0 -----END PGP SIGNATURE-----
There's not enough information here to give any useful answer.
A replicatable test case would be nice, or at least show us the queries
that are taking a long time and the table schemas they are working on.
regards, tom lane