On Thu, 20 Apr 2017 07:56:18 -0700, Rj Ewing <ewing.rj@gmail.com>
wrote:
>On Wed, Apr 19, 2017 at 6:44 PM, George Neuner <gneuner2@comcast.net> wrote:
>>
>> If you can restrict the FTS query to certain keys:
>>
>> SELECT id FROM mytable
>> WHERE tsquery( ... ) @@ to_tsvector(v)
>> AND k IN ( ... )
>> GROUP BY id
>>
>> [note: according to David Rowley, GROUP BY may be parallelized
>> whereas DISTINCT currently cannot be.]
>>
>> then given an index on 'k' it may be much faster than just the FTS
>> query alone. Subject to key variability, it also may be improved by
>> table partitioning to reduce the search space.
>>
>> If the FTS query is key restricted, you can parallelize either on the
>> client or on the server. If the FTS query is not key restricted, you
>> pretty much are limited to server side (and 9.6 or later).
>>
>> ?I'll look into parallelism if we can't get the performance we need.
>
>What do you mean if I can restrict the FTS query to certain keys? I'm not
>a sql expert, but it seems like the above query would match multiple keys
>to 1 tsquery value
You weren't specific as to the types of queries you wanted ... you
mentioned somewhere higher up in the discussion:
> ... a basic full text query on 44 million row is taking aproxx. 20ms.
That implied you wanted to FTS search every row. Only later did you
give an example that tied FTS patterns to particular keys. Until you
did that, there was no reason to assume the FTS search was targeted -
you might have wanted e.g., records where *any* k:v value matched the
FTS pattern.
[The take away here is: "try to be as specific as possible". 8-) ]
Obviously you can associate a FTS pattern with a particular key value
- just AND the conditions in the WHERE or HAVING clauses.
But be aware that, in general, the more conditions you place on a
query, the slower it runs.
George