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 the limit 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
On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bill Martin <bill.martin@communote.com> writes:
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.
Is this documented anywhere? I couldn't find it. If not, which
section would be the best one to add it to?
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Is this documented anywhere? I couldn't find it. If not, which
section would be the best one to add it to?
It's not documented, mainly because it hasn't reached the level of being
a supported feature. I'd like to figure out how to get pg_dump to dump
such settings before we call it supported. (The stumbling block is
exactly that index column names aren't set in stone, so it's not clear
that the ALTER command would do the right thing on dump-and-reload.)
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 have system-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
On Sun, Sep 16, 2012 at 06:16:55PM -0400, Tom Lane wrote:
Jeff Janes <jeff.janes@gmail.com> writes:
On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.Is this documented anywhere? I couldn't find it. If not, which
section would be the best one to add it to?It's not documented, mainly because it hasn't reached the level of being
a supported feature. I'd like to figure out how to get pg_dump to dump
such settings before we call it supported. (The stumbling block is
exactly that index column names aren't set in stone, so it's not clear
that the ALTER command would do the right thing on dump-and-reload.)
Is this TODO?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +