PFC wrote:
Hi, I am looking to improve the initial query speed for the following
query:
Try Xapian full text search engine, it behaves much better than
tsearch when the dataset exceeds your memory cache size.
__ Information from ESET NOD32 Antivirus, version of virus
signature d
Good idea. Note that Postgres is already doing this to some extent
with TOAST - read
http://www.postgresql.org/docs/8.3/interactive/storage-toast.html -
unfortunately, there doesn't seem to be an option to always move
particular columns out to TOAST. Your idea will produce an even
smaller ta
Actually, the index returns page numbers in the table on disc which
may contain one or more rows that are relevant. Postgres has to fetch
the whole row to find out the email_id and any other information,
including whether the row is visible in your current transaction
(concurrency control c
Actually, the index returns page numbers in the table on disc which
may contain one or more rows that are relevant. Postgres has to fetch
the whole row to find out the email_id and any other information,
including whether the row is visible in your current transaction
(concurrency control com
Matthew Wakeling wrote:
On Tue, 17 Jun 2008, Howard Cole wrote:
They both appear to do a scan on the email table (Why?).
The indexes don't contain copies of the row data. They only contain
pointers to the rows in the table. So once the index has been
consulted, Postgres still needs to
As far as I can see, that shouldn't make any difference. Both queries
still do the bitmap heap scan, and have almost exactly the same cost.
Matthew
You may have a point there Matthew, they both appear to do a scan on the
email table (Why?). But for whatever reason, I swear the second method
I think I may have answered my own question partially, the problem may
be how I structure the query.
I always structured my tsearch queries as follows following my initial
read of the tsearch2 instructions...
select email_id from email, to_tsquery('default', 'howard') as q where
q@@fts;
Ho
Alan Hodgson wrote:
It's because everything is cached, in particular the relevant rows from
the "email" table (accessing which took 22 of the original 27 seconds).
The plan looks good for what it's doing.
I don't see that query getting much faster unless you could add a lot more
cache RAM; 30
Hi, I am looking to improve the initial query speed for the following query:
select email_id from email, to_tsquery('default','example') as q where
q@@fts;
This is running on 8.2.4 on Windows Server 2K3.
The initial output from explain analyse is as follows.
"Nested Loop (cost=8.45..76.70 r
