public inbox for [email protected]
help / color / mirror / Atom feedRe: Planner selects different execution plans depending on limit
9+ messages / 5 participants
[nested] [flat]
* Re: Planner selects different execution plans depending on limit
@ 2012-09-13 14:42 Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bill Martin @ 2012-09-13 14:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>
> Tom Lane <[email protected]> writes:
>> Bill Martin <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
@ 2012-09-13 14:48 ` Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Jesper Krogh @ 2012-09-13 14:48 UTC (permalink / raw)
To: Bill Martin <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
@ 2012-09-13 14:54 ` Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2012-09-13 14:54 UTC (permalink / raw)
To: Jesper Krogh <[email protected]>; +Cc: Bill Martin <[email protected]>; [email protected] <[email protected]>
Jesper Krogh <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
@ 2012-09-13 17:19 ` Bill Martin <[email protected]>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bill Martin @ 2012-09-13 17:19 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>
> Tom Lane <[email protected]> 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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
@ 2012-09-13 17:33 ` Tom Lane <[email protected]>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <[email protected]>
2012-09-18 07:28 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2012-09-13 17:33 UTC (permalink / raw)
To: Bill Martin <[email protected]>; +Cc: [email protected] <[email protected]>
Bill Martin <[email protected]> writes:
> Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
@ 2012-09-16 21:39 ` Jeff Janes <[email protected]>
2012-09-16 22:16 ` Re: [PERFORM] Planner selects different execution plans depending on limit Tom Lane <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2012-09-16 21:39 UTC (permalink / raw)
To: pgsql-docs; +Cc: Bill Martin <[email protected]>; Tom Lane <[email protected]>
On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <[email protected]> wrote:
> Bill Martin <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <[email protected]>
@ 2012-09-16 22:16 ` Tom Lane <[email protected]>
2012-09-26 15:52 ` Re: [PERFORM] Planner selects different execution plans depending on limit Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2012-09-16 22:16 UTC (permalink / raw)
To: Jeff Janes <[email protected]>; +Cc: pgsql-docs; Bill Martin <[email protected]>
Jeff Janes <[email protected]> writes:
> On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <[email protected]>
2012-09-16 22:16 ` Re: [PERFORM] Planner selects different execution plans depending on limit Tom Lane <[email protected]>
@ 2012-09-26 15:52 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2012-09-26 15:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jeff Janes <[email protected]>; pgsql-docs; Bill Martin <[email protected]>
On Sun, Sep 16, 2012 at 06:16:55PM -0400, Tom Lane wrote:
> Jeff Janes <[email protected]> writes:
> > On Thu, Sep 13, 2012 at 10:33 AM, Tom Lane <[email protected]> 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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <[email protected]>
@ 2012-09-18 07:28 ` Bill Martin <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Bill Martin @ 2012-09-18 07:28 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>
Tom Lane <mailto:[email protected]> writes:
> Bill Martin <[email protected]> writes:
>> Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-09-26 15:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <[email protected]>
2012-09-13 14:48 ` Jesper Krogh <[email protected]>
2012-09-13 14:54 ` Tom Lane <[email protected]>
2012-09-13 17:19 ` Bill Martin <[email protected]>
2012-09-13 17:33 ` Tom Lane <[email protected]>
2012-09-16 21:39 ` Jeff Janes <[email protected]>
2012-09-16 22:16 ` Tom Lane <[email protected]>
2012-09-26 15:52 ` Bruce Momjian <[email protected]>
2012-09-18 07:28 ` Bill Martin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox