On Tue, Aug 2, 2011 at 2:22 AM, Jan Wielgus <jan_w(at)tlen(dot)pl> wrote:
> So, there is apparently a problem with vector concatenating - the indexes don't work then. I tried to use the vectors separately and to make 'OR' comparison between single vector @@ ts_query checks,
> but it didn't help very much (performance was better, but still over 20 sec):
> (participant.participant_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))) OR (person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail')))
> Is there a way to make this work with better performance? Or is it necessary to create a single vector that contains data from multiple tables and then add an index on it? It would be so far problematic for us,
> because we are using multiple complex queries with variable number of selected columns. I know that another solution might be an union among multiple queries, every of which uses a single vector,
> but this solution is inconvenient too.
Only something of the form 'indexed-column indexable-operator value'
is going to be indexable. So when you concatenate two columns from
different tables - as you say - not indexable.
In general, OR-based conditions that cross table boundaries tend to be
expensive, because they have to be applied only after performing the
join. You can't know for sure looking only at a row from one table
whether or not it will be needed, so you have to join them all and
then filter the results.
The Enterprise PostgreSQL Company
In response to
pgsql-performance by date
|Next:||From: Merlin Moncure||Date: 2011-10-24 21:31:31|
|Subject: Re: Choosing between Intel 320, Intel 510 or OCZ Vertex 3
SSD for db server|
|Previous:||From: David Boreham||Date: 2011-10-24 16:53:34|
|Subject: Re: Choosing between Intel 320, Intel 510 or OCZ Vertex
3 SSD for db server|