Обсуждение: Re: Planner selects different execution plans depending on limit

> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Bill Martin <bill.martin@communote.com> writes:
>> I've tried different values for the statistics but it is all the same (the planner decide to switch to a seqscan if
thelimit is 10).
 

>> ALTER TABLE core_content ALTER column content SET STATISTICS 1000;

> Um, did you actually do an ANALYZE after changing that?
> 
>             regards, tom lane

Yes, I've run the ANALYZE command.

Regards,
Bill Martin

On 13/09/12 16:42, Bill Martin wrote:
> Yes, I've run the ANALYZE command. Regards, Bill Martin
The main problem in your case is actually that you dont store the
tsvector in the table.

If you store to_tsvector('simple',content.content) in a column in
the database and search against that instead
then you'll allow PG to garther statistics on the column and make the
query-planner act according to that.

Jesper

Jesper Krogh <jesper@krogh.cc> writes:
> On 13/09/12 16:42, Bill Martin wrote:
>> Yes, I've run the ANALYZE command. Regards, Bill Martin

> The main problem in your case is actually that you dont store the
> tsvector in the table.

Oh, duh, obviously I lack caffeine this morning.

> If you store to_tsvector('simple',content.content) in a column in
> the database and search against that instead
> then you'll allow PG to garther statistics on the column and make the
> query-planner act according to that.

He can do it without having to change his schema --- but it's the index
column, not the underlying content column, that needs its statistics
target adjusted.

            regards, tom lane

> Tom Lane <tgl@sss.pgh.pa.us> writes:

> He can do it without having to change his schema --- but it's the index
> column, not the underlying content column, that needs its statistics
> target adjusted.

>                         regards, tom lane

How can I adjust the statistics target of the index?

Bill Martin <bill.martin@communote.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> He can do it without having to change his schema --- but it's the index
>> column, not the underlying content column, that needs its statistics
>> target adjusted.

> How can I adjust the statistics target of the index?

Just pretend it's a table.

    ALTER TABLE index_name ALTER COLUMN column_name SET STATISTICS ...

You'll need to look at the index (eg with \d) to see what the name of
the desired column is, since index expressions have system-assigned
column names.

            regards, tom lane

Tom Lane <mailto:tgl@sss.pgh.pa.us> writes:
> Bill Martin <bill.martin@communote.com> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> He can do it without having to change his schema --- but it's the 
>>> index column, not the underlying content column, that needs its 
>>> statistics target adjusted.

>> How can I adjust the statistics target of the index?

> Just pretend it's a table.

>    ALTER TABLE index_name ALTER COLUMN column_name SET STATISTICS ...

> You'll need to look at the index (eg with \d) to see what the name of the desired column is, since index expressions
havesystem-assigned
 
> column names.

>        regards, tom lane

I tried: 
ALTER TABLE ft_simple_core_content_content_idx ALTER column to_tsvector SET STATISTICS 10000;
ANALYZE;

and
REINDEX INDEX ft_simple_core_content_content_idx;

All the trouble was for nothing.

Are there any other possibilities to solve my problem?

Best regards,
Bill Martin