[SQL] Index Usage and Running Times by FullTextSearch with prefix matching
Hi
I tested the following:
CREATE TABLE t1
(
id serial NOT NULL,
a character varying(125),
a_tsvector tsvector,
CONSTRAINT t1_pkey PRIMARY KEY (id)
);
INSERT INTO t1 (a, a_tsvector)
VALUES ('o,p,f,j,z,j',
to_tsvector('o,p,f,j,z,j');
CREATE INDEX a_tsvector_idx ON t1 USING gin (a_tsvector);
(I have generated 90 records with random words like this)
Now querying: normal full text search
SELECT count(a)
FROM t1
WHERE a_tsvector @@ to_tsquery('a & b & c & d')
(RESULT: count: 619)
Total query runtime: 353 ms.
Query Plan:
"Aggregate (cost=6315.22..6315.23 rows=1 width=36)"
" -> Bitmap Heap Scan on t1 (cost=811.66..6311.46 rows=1504 width=36)"
"Recheck Cond: (a_tsvector @@ to_tsquery('a & b & c &
d'::text))"
"-> Bitmap Index Scan on a_tsvector_idx (cost=0.00..811.28
rows=1504 width=0)"
" Index Cond: (a_tsvector @@ to_tsquery('a & b & c
& d'::text))"
And querying: FTS with prefix matching:
SELECT count(a)
FROM t1
WHERE a_tsvector @@ to_tsquery('aaa:* & b:* & c:* & d:*')
(RESULT: count: 619)
Total query runtime: 21266 ms.
Query Plan:
"Aggregate (cost=804.02..804.03 rows=1 width=36)"
" -> Bitmap Heap Scan on t1 (cost=800.00..804.02 rows=1 width=36)"
"Recheck Cond: (a_tsvector @@ to_tsquery('aaa:* & b:* & c:* &
d:*'::text))"
"-> Bitmap Index Scan on a_tsvector_idx (cost=0.00..800.00 rows=1
width=0)"
" Index Cond: (a_tsvector @@ to_tsquery('aaa:* & b:* & c:* &
d:*'::text))"
I don't understand the big query time difference, despite the explainig
index usage.
NOnetheless I'd like to simulate LIKE 'aaa%' with full text search. Would I
have a better sollution?
Many thanks in advance!
Rawi
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Index Usage and Running Times by FullTextSearch with prefix matching
rawi writes:
> And querying: FTS with prefix matching:
> SELECT count(a)
> FROM t1
> WHERE a_tsvector @@ to_tsquery('aaa:* & b:* & c:* & d:*')
> (RESULT: count: 619)
> Total query runtime: 21266 ms.
FWIW, I get fairly decent performance for cases like this in HEAD
(at least with a GIN index; GIST seems much less able to do well with
short prefixes). What PG version are you testing?
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Index Usage and Running Times by FullTextSearch with prefix matching
Tom Lane-2 wrote > FWIW, I get fairly decent performance for cases like this in HEAD > (at least with a GIN index; GIST seems much less able to do well with > short prefixes). What PG version are you testing? Thank you Tom, I'm testing on PG 9.1 on UbuntuServer 12.10, 64bit I'll update to 9.2 the next days and try again. Kind Regards Rawi -- View this message in context: http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021p5759045.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Index Usage and Running Times by FullTextSearch with prefix matching
Tom Lane-2 wrote
> FWIW, I get fairly decent performance for cases like this in HEAD
> (at least with a GIN index; GIST seems much less able to do well with
> short prefixes).
Short or long prefixes seem to be equaly unfavorable. Even with the full
length of the words, but queried as prefix I get a runtime of 25342ms
compared to 353ms without prefixes:
SELECT count(a)
FROM t1
WHERE a_tsvector @@ to_tsquery('a:* & b:* & c:* & d:*')
Total query runtime: 25342 ms
"Aggregate (cost=804.02..804.03 rows=1 width=36)"
" -> Bitmap Heap Scan on t1 (cost=800.00..804.02 rows=1 width=36)"
"Recheck Cond: (a_tsvector @@ to_tsquery('a:* & b:* &
c:* & d:*'::text))"
"-> Bitmap Index Scan on a_tsvector_idx (cost=0.00..800.00 rows=1
width=0)"
" Index Cond: (a_tsvector @@ to_tsquery('a:* & b:* &
c:* & d:*'::text))"
Kind Regards
Rawi
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021p5759046.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select clause in JOIN statement
Is it valid to specify a SELECT statement as part of a JOIN clause? For example: SELECT table1.f1, table1.f2 FROM table1 INNER JOIN (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = table_aux.f1 Respectfully, Jorge Maldonado
Re: [SQL] Select clause in JOIN statement
It works. Also consider views. Just used this on a my db: SELECT * FROM tblcus_customer INNER JOIN ( SELECT * FROM tblcus_customer_status WHERE status_id > 0) AS b ON tblcus_customer.status = b.status_id You can even join with a function result. Regards, Luca. 2013/6/14 JORGE MALDONADO : > Is it valid to specify a SELECT statement as part of a JOIN clause? > > For example: > > SELECT table1.f1, table1.f2 FROM table1 > INNER JOIN > (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = > table_aux.f1 > > Respectfully, > Jorge Maldonado -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
