Re: [SQL] effectiveness tool
Your best bet is probably EXPLAIN ANALYZE. This should give you a better idea of where the inefficiencies are in your queries. The only way to make it more accurate is, as far as I know, increasing the number of rows postgresql samples from each table during the vacuum. Stefano - Stefano Buliani Covestor email: [EMAIL PROTECTED] mobile: +44 (0) 7766295328 This message is intended solely for the recipient(s) to whom it is addressed. If you are not the intended recipient, you should not disclose, distribute or copy this email. Please notify the sender immediately and delete this email from your system. - Original Message - From: "Judith Altamirano" <[EMAIL PROTECTED]> To: Sent: Tuesday, October 14, 2008 11:34 PM Subject: [SQL] effectiveness tool Hello everybody!! I just want to know if there be some tool to analize the performance of a query, I mean to qualify the effectiveness, speed, if I have the correct indexes. Hope somebody can help me Thanks in advanced!! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating Query of 1 table from data in another
On Wed, Oct 15, 2008 at 8:16 AM, Chris Preston <[EMAIL PROTECTED]> wrote: > > Thanks scott > Does this work with 8.1 (that's what I'm using) > When I try to create the b table I get a message Yep. Post a self-contained example of what's not working. > ERROR: there is no unique constraint matching given keys for referenced > table "testa" > SQL state: 42830 > > I named the a table testa and the b table testb Like it says, there's no matching keys. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Most efficient way to achieve this ts_query
Hi If someone uses a search query on my site like this: "abc def" I would like to return all results for 'abc & def' first, followed by all results for tsquery 'abc | def' is there some way to express this in one tsquery? What's the most efficient way to go about this? The search is on one column. Thanks Jamie -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Most efficient way to achieve this ts_query
Jamie Tufnell wrote: If someone uses a search query on my site like this: "abc def" I would like to return all results for 'abc & def' first, followed by all results for tsquery 'abc | def' is there some way to express this in one tsquery? What's the most efficient way to go about this? The search is on one column. SELECT * FROM table WHERE field='abc' OR field~'def' ORDER BY CASE WHERE field~'abc' AND field~'def' THEN 1 ELSE 0 END DESC; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Search fields in multiple tables
Hi, I have some information about books spread over multiple tables (title, authors, ISBN, blurb, publisher, etc.) Is there any convenient way in PostgreSQL to allow a user to search these in a single operation? If there is, would there be some way to assign weights to the fields? E.g. a match on title would rate higher than a match on publisher or on blurb. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Most efficient way to achieve this ts_query
On 10/16/08, Frank Bax <[EMAIL PROTECTED]> wrote: > Jamie Tufnell wrote: >> If someone uses a search query on my site like this: >> >> "abc def" >> >> I would like to return all results for 'abc & def' first, followed by >> all results for tsquery 'abc | def' is there some way to express this >> in one tsquery? What's the most efficient way to go about this? The >> search is on one column. > > > SELECT * FROM table WHERE field='abc' OR field~'def' > ORDER BY CASE WHERE field~'abc' AND field~'def' THEN 1 ELSE 0 END DESC; I am using tsqueries though (to_tsquery() and to_tsvector()) to benefit from stemming. I understand how your approach might still apply, but I'm curious to know if that's the best way, or can it be done in a single to_tsquery(), with a single MATCH ? Thanks, Jamie -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
