[SQL] Full text search ordering question
Hi, is it possible to order the results of a full text search using another
field?
for example with the following table:
CREATE TABLE breadcrumbs (
node_id integer NOT NULL,
breadcrumb character varying,
textsearchable tsvector,
views integer,
CONSTRAINT pk_breadcrumbs PRIMARY KEY (node_id)
)
I'd like to do something like this
select node_id, views from breadcrumbs where textsearchable @@
to_tsquery('word') order by views desc limit 100;
As such I'd like to create a fts index on the textsearchable field and views
field such that it orders the results by the views column.
atm, this table has over 3M rows (and is likely to b magnitudes bigger) and
some words match hundreds of thousands of rows, The best i've got so far is
to create a fts index which is used and then the resulting rows are sorted
in memory. Unfortunately because of the number of rows returned this takes a
few seconds.
With a btree index i could index on the 2 columns and it would only hit the
index and take a fraction of a second.
I've tried the btree_gist module, but it doesn't make any difference (except
in letting me use an int in the gist index)
Any ideas or is this simply not possible?
Thanks
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sequence and nextval problem
At 11:20 PM 11/24/2008, [EMAIL PROTECTED] wrote:
Message-Id: <[EMAIL PROTECTED]>
From: ries van Twisk <[EMAIL PROTECTED]>
To: Tk421 <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
Subject: Re: Sequence and nextval problem
Date: Mon, 24 Nov 2008 16:21:40 -0500
References: <[EMAIL PROTECTED]>
X-Archive-Number: 200811/144
X-Sequence-Number: 31928
On Nov 24, 2008, at 2:12 PM, Tk421 wrote:
The conversion from access database to postgres worked fine.
Everithing it's ok. But now, when i use my database i've found a
problem with sequences. In the conversion, the "autonumeric" fields
from access have been converted to sequences, everithing ok in a
first view. The problem comes because the autonumeric fields in
access always return the last value of the table +1, but postgres
no. Postgres returns "lost" (i don't know how to call them) values.
An example.
[snip]
In access if i execute "INSERT INTO table (description) VALUES
('desc 8'), the result row is 8 | desc 8
But in postgres the same query te result row is 3 | desc 8
My question is, can i do something to make ANY sequence to take
the last value from his associated table, and not a "lost" value?
This sounds like if the start of the sequence is set incorrectly:
Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);
btw, you should also not expect a specific value from the sequence
except that you will always get the next value from the sequence.
it's also generally a bad idea to do select max(someid)+1 from
table.
The whole concept of a sequence is thus much better.
I think this is sound general advice for a production database.
However if you control the database such that you can prevent access to
it while you are updating it, you can run something like:
SELECT setval('NAME OF SEQUENCE', (select max(id)+1 from
table_of_sequence), true);
Where "table_of_sequence" is the name of the table which the sequence
is attached to.
The reason you don't use that syntax is that it's not multi-user safe.
But if you know there are no other users running changes to that
sequence when you run your updates, then you're good to go. It's a very
fast way to update all your tables to make sure the sequence #'s are
all valid, without having to look up the max value on each one (which
would also require that you shut off access to the table and for a much
longer time).
Hope that helps,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Full text search ordering question
John,
it's a good tradition to include query and their EXPLAIN ANALYZE.
Pg version is also useful.
Did you try GIN index ?
In 8.4 you can use gin index on (views,tsvector)
Oleg
On Tue, 25 Nov 2008, John Lister wrote:
Hi, is it possible to order the results of a full text search using another
field?
for example with the following table:
CREATE TABLE breadcrumbs (
node_id integer NOT NULL,
breadcrumb character varying,
textsearchable tsvector,
views integer,
CONSTRAINT pk_breadcrumbs PRIMARY KEY (node_id)
)
I'd like to do something like this
select node_id, views from breadcrumbs where textsearchable @@
to_tsquery('word') order by views desc limit 100;
As such I'd like to create a fts index on the textsearchable field and views
field such that it orders the results by the views column.
atm, this table has over 3M rows (and is likely to b magnitudes bigger) and
some words match hundreds of thousands of rows, The best i've got so far is
to create a fts index which is used and then the resulting rows are sorted in
memory. Unfortunately because of the number of rows returned this takes a few
seconds.
With a btree index i could index on the 2 columns and it would only hit the
index and take a fraction of a second.
I've tried the btree_gist module, but it doesn't make any difference (except
in letting me use an int in the gist index)
Any ideas or is this simply not possible?
Thanks
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Full text search ordering question
Thanks for the reply and apologies, it was my first post.
I'm running on PG 8.3.3 (ubuntu), i haven't tried gin as that doesn't
support multi-column. I haven't used 8.4 as it is still in development?
a sample query is as follows
select node_id from breadcrumbs where textsearchable @@ to_tsquery('book')
order by views desc limit 100;
explain analyze results in this:
Limit (cost=10300.58..10300.83 rows=100 width=381) (actual
time=69887.851..69887.880 rows=100 loops=1)
-> Sort (cost=10300.58..10307.61 rows=2812 width=381) (actual
time=69887.849..69887.862 rows=100 loops=1)
Sort Key: views
Sort Method: top-N heapsort Memory: 84kB
-> Bitmap Heap Scan on breadcrumbs (cost=171.49..10193.10
rows=2812 width=381) (actual time=60311.197..69574.742 rows=569519 loops=1)
Filter: (textsearchable@@ to_tsquery('book'::text))"
-> Bitmap Index Scan on idx_breadcr (cost=0.00..170.79
rows=2812 width=0) (actual time=60261.959..60261.959 rows=569519 loops=1)
Index Cond: (textsearchable @@ to_tsquery('book'::text))
Total runtime: 69896.896 ms
As you can see it sorts the full result set from the search. Ideally i'd
like to use an index on the views.
How stable is 8.4? Is it worth trying that or is the multi-column gin likely
to be back-ported?
Thanks
John,
it's a good tradition to include query and their EXPLAIN ANALYZE. Pg
version is also useful.
Did you try GIN index ?
In 8.4 you can use gin index on (views,tsvector)
Oleg
On Tue, 25 Nov 2008, John Lister wrote:
Hi, is it possible to order the results of a full text search using
another field?
for example with the following table:
CREATE TABLE breadcrumbs (
node_id integer NOT NULL,
breadcrumb character varying,
textsearchable tsvector,
views integer,
CONSTRAINT pk_breadcrumbs PRIMARY KEY (node_id)
)
I'd like to do something like this
select node_id, views from breadcrumbs where textsearchable @@
to_tsquery('word') order by views desc limit 100;
As such I'd like to create a fts index on the textsearchable field and
views field such that it orders the results by the views column.
atm, this table has over 3M rows (and is likely to b magnitudes bigger)
and some words match hundreds of thousands of rows, The best i've got so
far is to create a fts index which is used and then the resulting rows
are sorted in memory. Unfortunately because of the number of rows
returned this takes a few seconds.
With a btree index i could index on the 2 columns and it would only hit
the index and take a fraction of a second.
I've tried the btree_gist module, but it doesn't make any difference
(except in letting me use an int in the gist index)
Any ideas or is this simply not possible?
Thanks
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] EXECUTE query INTO problem
I've got a problem with a function: It receives two parameters, the first, the table name, and the second, a where condition. The function looks like this: DECLARE cod bigint; query TEXT; BEGIN query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2; EXECUTE query INTO cod; · · · END; I've alwais get the same error, in the EXECUTE sentence: it says: Error at or near NULL at character X I've also tried declaring cod as row, but the error is the same. Anybody can help me? Thank you very much -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] EXECUTE query INTO problem
Tk421 <[EMAIL PROTECTED]> schrieb: >I've got a problem with a function: It receives two parameters, the > first, the table name, and the second, a where condition. The function > looks like this: > >DECLARE > cod bigint; > query TEXT; > >BEGIN > query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2; > > EXECUTE query INTO cod; > · > · > · >END; > >I've alwais get the same error, in the EXECUTE sentence: it says: > Error at or near NULL at character X Wild guess: one or both parameters contains nothing, NULL. If you concat a string with NULL, the result is NULL. And you can't execute a NULL-command. > >I've also tried declaring cod as row, but the error is the same. > >Anybody can help me? Please show us the complete function and how do you call this function. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] EXECUTE query INTO problem
2008/11/25 Tk421 <[EMAIL PROTECTED]>:
> I've got a problem with a function: It receives two parameters, the first,
> the table name, and the second, a where condition. The function looks like
> this:
>
> DECLARE
> cod bigint;
> query TEXT;
>
> BEGIN
> query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2;
>
> EXECUTE query INTO cod;
> ·
> ·
> ·
> END;
>
> I've alwais get the same error, in the EXECUTE sentence: it says: Error at
> or near NULL at character X
>
> I've also tried declaring cod as row, but the error is the same.
>
> Anybody can help me?
>
> Thank you very much
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
CREATE OR REPLACE FUNCTION test(character varying, character varying)
RETURNS integer AS
$BODY$declare
r_int int;
q varchar;
begin
if $1 is not null and $2 is not null then
q = 'select p1 from '||$1||' where '||$2;
execute q into r_int;
return r_int;
else
return null;
end if;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION test(character varying, character varying) OWNER TO merlin;
and simple table ;]
merlin=> \d t1
Table "public.t1"
Column | Type | Modifiers
+---+---
p1 | integer |
p2 | character varying(32) |
merlin=> insert into t1 values(2, 'abc');INSERT 0 1
merlin=> select test('t1', ' p2= ''abc''')
;
test
--
2
(1 row)
merlin=> select test(null, ' p2= ''abc''')
;
test
--
(1 row)
And all its works
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
