Subject: Re: Planner selects different execution plans depending on limit

From: Bill Martin <[email-masked]>
Date: Thu, 13 Sep 2012 14:42:07 +0000

> Tom Lane <[email-masked]> writes:
>> Bill Martin <[email-masked]> 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

---

From: Jesper Krogh <[email-masked]>
Date: Thu, 13 Sep 2012 16:48:07 +0200

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 13 Sep 2012 10:54:19 -0400

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

Oh, duh, obviously I lack caffeine this morning.

> 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

---

From: Bill Martin <[email-masked]>
Date: Thu, 13 Sep 2012 17:19:10 +0000

>                         regards, tom lane

How can I adjust the statistics target of the index?

---

From: Tom Lane <[email-masked]>
Date: Thu, 13 Sep 2012 13:33:19 -0400

> 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

---

From: Jeff Janes <[email-masked]>
Date: Sun, 16 Sep 2012 14:39:34 -0700

> the desired column is, since index expressions have system-assigned

Is this documented anywhere?  I couldn't find it.  If not, which
section would be the best one to add it to?

Cheers,

Jeff

---

From: Tom Lane <[email-masked]>
Date: Sun, 16 Sep 2012 18:16:55 -0400

> 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

---

From: Bill Martin <[email-masked]>
Date: Tue, 18 Sep 2012 07:28:25 +0000

Tom Lane <mailto:[email-masked]> 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.

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 26 Sep 2012 11:52:12 -0400

> that the ALTER command would do the right thing on dump-and-reload.)

Is this  TODO?

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +