pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner selects different execution plans depending on limit
15+ messages / 6 participants
[nested] [flat]
* Planner selects different execution plans depending on limit
@ 2012-09-10 14:24 bill_martin@freenet.de
2012-09-10 18:18 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 15+ messages in thread
From: bill_martin@freenet.de @ 2012-09-10 14:24 UTC (permalink / raw)
To: pgsql-performance
Hi All
I´ve ft_simple_core_content_content_idx
ON core_content
USING gin
(to_tsvector('simple'::regconfig, content) );
If I´m seaching for a word which is NOT in the column content the query plan and the execution time differs with the given limit.
If I choose 3927 or any higher number the query execution took only few milliseconds.
core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=0.00..19302.23 rows=3926 width=621) (actual time=52147.149..52147.149 rows=0 loops=1)"
" -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011 width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
" Filter: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
"Total runtime: 52147.173 ms"
Is there any posibility to improve the performance even if the limit is only 10? Is it possible to determine that the query optimizer takes only the fast bitmap heap scan instead of the slow seq scan?
Regards,
Bill Martin
---
E-Mail ist da wo du bist! Jetzt mit freenetMail ganz bequem auch unterwegs E-Mails verschicken.
Am besten gleich informieren unter http://mail.freenet.de/mobile-email/index.html
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-10 14:24 Planner selects different execution plans depending on limit bill_martin@freenet.de
@ 2012-09-10 18:18 ` Jesper Krogh <jesper@krogh.cc>
0 siblings, 0 replies; 15+ messages in thread
From: Jesper Krogh @ 2012-09-10 18:18 UTC (permalink / raw)
To: pgsql-performance
On 10/09/12 16:24, bill_martin@freenet.de wrote:
>
> Hi All
>
> I´ve ft_simple_core_content_content_idx
> ON core_content
> USING gin
> (to_tsvector('simple'::regconfig, content) );
>
>
> If I´m seaching for a word which is NOT in the column content the
> query plan and the execution time differs with the given limit.
> If I choose 3927 or any higher number the query execution took only
> few milliseconds.
>
> core_content content where
> to_tsvector('simple', content.content) @@
> tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=true
> Limit 3927
>
> "Limit (cost=0.00..19302.23 rows=3926 width=621) (actual
> time=52147.149..52147.149 rows=0 loops=1)"
> " -> Seq Scan on core_content content (cost=0.00..98384.34
> rows=20011 width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
> " Filter: (to_tsvector('simple'::regconfig, content) @@
> '''asdasdadas'':*'::tsquery)"
> "Total runtime: 52147.173 ms"
>
> Is there any posibility to improve the performance even if the limit
> is only 10? Is it possible to determine that the query optimizer takes
> only the fast bitmap heap scan instead of the slow seq scan?
>
The big hammer is: "set enable_seqscan = off", but if you tell which PG
version you're on there may be something to do. I suggest you'd start by
bumping the statistics target for the column to 10000 and run analyze to
see what that changes.
--
Jesper
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
@ 2012-09-11 07:20 Bill Martin <bill.martin@communote.com>
2012-09-11 17:19 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bill Martin @ 2012-09-11 07:20 UTC (permalink / raw)
To: pgsql-performance
On 10/09/12 16:24, bill_martin@freenet.de<mailto:bill_martin@freenet.de> wrote:
Hi All
I´ve ft_simple_core_content_content_idx
ON core_content
USING gin
(to_tsvector('simple'::regconfig, content) );
If I´m seaching for a word which is NOT in the column content the query plan and the execution time differs with the given limit.
If I choose 3927 or any higher number the query execution took only few milliseconds.
core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=0.00..19302.23 rows=3926 width=621) (actual time=52147.149..52147.149 rows=0 loops=1)"
" -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011 width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
" Filter: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
"Total runtime: 52147.173 ms"
Is there any posibility to improve the performance even if the limit is only 10? Is it possible to determine that the query optimizer takes only the fast bitmap heap scan instead of the slow seq scan?
The big hammer is: "set enable_seqscan = off", but if you tell which PG version you're on there may be something to do. I suggest you'd start by bumping the statistics target for the column to 10000 and run analyze to see what that changes.
--
Jesper
Hi,
my email client delete a lot of the content of the original thread message. Here is the full content:
Hi All
I´ve created following table which contains one million records.
CREATE TABLE core_content
(
id bigint NOT NULL,
content text NOT NULL,
short_content text,
CONSTRAINT core_content_pkey PRIMARY KEY (id )
)
CREATE INDEX ft_simple_core_content_content_idx
ON core_content
USING gin
(to_tsvector('simple'::regconfig, content) );
If I´m seaching for a word which is not in the column content the query plan and the execution time differs with the given limit.
If I choose 3927 or any higher number the query execution took only few milliseconds.
select * from core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual time=0.255..0.255 rows=0 loops=1)"
" -> Bitmap Heap Scan on core_content content (cost=10091.09..57046.32 rows=20011 width=621) (actual time=0.254..0.254 rows=0 loops=1)"
" Recheck Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
" -> Bitmap Index Scan on ft_simple_core_content_content_idx (cost=0.00..10086.09 rows=20011 width=0) (actual time=0.251..0.251 rows=0 loops=1)"
" Index Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
"Total runtime: 0.277 ms"
If I choose 3926 or any lower number (e.g. 10) the query execution took more than fifty seconds.
select * from core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=0.00..19302.23 rows=3926 width=621) (actual time=52147.149..52147.149 rows=0 loops=1)"
" -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011 width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
" Filter: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
"Total runtime: 52147.173 ms"
Is there any posibility to tune up the performance even if the limit is only 10? Is it possible to determine that the query optimizer takes
only the fast bitmap heap scan instead of the slow seq scan?
I use PostgreSQL 9.1.5.; Intel i5-2400 @ 3.1 GHz, 16GB; Windows 7 64 Bit
Regards,
Bill Martin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-11 07:20 Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
@ 2012-09-11 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2012-09-11 17:19 UTC (permalink / raw)
To: Bill Martin <bill.martin@communote.com>; +Cc: pgsql-performance
Bill Martin <bill.martin@communote.com> writes:
> I´ve created following table which contains one million records.
> ...
> "Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual time=0.255..0.255 rows=0 loops=1)"
> " -> Bitmap Heap Scan on core_content content (cost=10091.09..57046.32 rows=20011 width=621) (actual time=0.254..0.254 rows=0 loops=1)"
> " Recheck Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
> " -> Bitmap Index Scan on ft_simple_core_content_content_idx (cost=0.00..10086.09 rows=20011 width=0) (actual time=0.251..0.251 rows=0 loops=1)"
> " Index Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
> "Total runtime: 0.277 ms"
> Is there any posibility to tune up the performance even if the limit is only 10?
The problem is the way-off rowcount estimate (20011 rows when it's
really none); with a smaller estimate there, the planner wouldn't decide
to switch to a seqscan.
Did you take the advice to increase the column's statistics target?
Because 20011 looks suspiciously close to the default estimate that
tsquery_opr_selec will fall back on if it hasn't got enough stats
to come up with a trustworthy estimate for a *-pattern query.
(I think there are probably some bugs in tsquery_opr_selec's estimate
for this, as I just posted about on pgsql-hackers. But this number
looks like you're not even getting to the estimation code, for lack
of enough statistics entries.)
The other thing that seems kind of weird here is that the cost estimate
for the bitmap index scan seems out of line even given the
20000-entries-to-fetch estimate. I'd have expected a cost estimate of a
few hundred for that, not 10000. Perhaps this index is really bloated,
and it's time to REINDEX it?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
@ 2012-09-13 09:05 Bill Martin <bill.martin@communote.com>
2012-09-13 14:04 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bill Martin @ 2012-09-13 09:05 UTC (permalink / raw)
To: pgsql-performance
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Bill Martin <bill(dot)martin(at)communote(dot)com> writes:
>> I´ve created following table which contains one million records.
>> ...
>> "Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual time=0.255..0.255 rows=0 loops=1)"
>> " -> Bitmap Heap Scan on core_content content (cost=10091.09..57046.32 rows=20011 width=621) (actual time=0.254..0.254 rows=0 loops=1)"
>> " Recheck Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
>> " -> Bitmap Index Scan on ft_simple_core_content_content_idx (cost=0.00..10086.09 rows=20011 width=0) (actual time=0.251..0.251 rows=0 loops=1)"
>> " Index Cond: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)"
>> "Total runtime: 0.277 ms"
>> Is there any posibility to tune up the performance even if the limit is only 10?
> The problem is the way-off rowcount estimate (20011 rows when it's
> really none); with a smaller estimate there, the planner wouldn't decide
> to switch to a seqscan.
>
> Did you take the advice to increase the column's statistics target?
> Because 20011 looks suspiciously close to the default estimate that
> tsquery_opr_selec will fall back on if it hasn't got enough stats
> to come up with a trustworthy estimate for a *-pattern query.
>
> (I think there are probably some bugs in tsquery_opr_selec's estimate
> for this, as I just posted about on pgsql-hackers. But this number
> looks like you're not even getting to the estimation code, for lack
> of enough statistics entries.)
>
> The other thing that seems kind of weird here is that the cost estimate
> for the bitmap index scan seems out of line even given the
> 20000-entries-to-fetch estimate. I'd have expected a cost estimate of a
> few hundred for that, not 10000. Perhaps this index is really bloated,
> and it's time to REINDEX it?
>
> regards, tom lane
Hi,
thank you for helping me.
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;
I also tried to reindex the index but the planner decide to switch to a seqscan.
REINDEX INDEX ft_simple_core_content_content_idx;
Disable the seqscan helps me but is this a good decision for all use cases?
SET enable_seqscan = off;
Are there any other possibilities to solve my problem?
Best regards,
Bill Martin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
2012-09-13 09:05 Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
@ 2012-09-13 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2012-09-13 14:04 UTC (permalink / raw)
To: Bill Martin <bill.martin@communote.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Planner selects different execution plans depending on limit
@ 2012-09-13 14:42 Bill Martin <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 15+ messages in thread
From: Bill Martin @ 2012-09-13 14:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
@ 2012-09-13 14:48 ` Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Jesper Krogh @ 2012-09-13 14:48 UTC (permalink / raw)
To: Bill Martin <bill.martin@communote.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
@ 2012-09-13 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2012-09-13 14:54 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Bill Martin <bill.martin@communote.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-09-13 17:19 ` Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bill Martin @ 2012-09-13 17:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> 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?
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
@ 2012-09-13 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <jeff.janes@gmail.com>
2012-09-18 07:28 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
0 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2012-09-13 17:33 UTC (permalink / raw)
To: Bill Martin <bill.martin@communote.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-09-16 21:39 ` Jeff Janes <jeff.janes@gmail.com>
2012-09-16 22:16 ` Re: [PERFORM] Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Jeff Janes @ 2012-09-16 21:39 UTC (permalink / raw)
To: pgsql-docs@postgresql.org; +Cc: Bill Martin <bill.martin@communote.com>; Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <jeff.janes@gmail.com>
@ 2012-09-16 22:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-26 15:52 ` Re: [PERFORM] Planner selects different execution plans depending on limit Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2012-09-16 22:16 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-docs@postgresql.org, Bill Martin <bill.martin@communote.com>
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
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-16 21:39 ` Re: [PERFORM] Planner selects different execution plans depending on limit Jeff Janes <jeff.janes@gmail.com>
2012-09-16 22:16 ` Re: [PERFORM] Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-09-26 15:52 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2012-09-26 15:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-docs@postgresql.org, Bill Martin <bill.martin@communote.com>
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. +
^ permalink raw reply [nested|flat] 15+ 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 <bill.martin@communote.com>
2012-09-13 14:48 ` Re: Planner selects different execution plans depending on limit Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Re: Planner selects different execution plans depending on limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-09-18 07:28 ` Bill Martin <bill.martin@communote.com>
1 sibling, 0 replies; 15+ messages in thread
From: Bill Martin @ 2012-09-18 07:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2012-09-26 15:52 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-10 14:24 Planner selects different execution plans depending on limit bill_martin@freenet.de
2012-09-10 18:18 ` Jesper Krogh <jesper@krogh.cc>
2012-09-11 07:20 Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-11 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 09:05 Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 14:42 Re: Planner selects different execution plans depending on limit Bill Martin <bill.martin@communote.com>
2012-09-13 14:48 ` Jesper Krogh <jesper@krogh.cc>
2012-09-13 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-13 17:19 ` Bill Martin <bill.martin@communote.com>
2012-09-13 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-16 21:39 ` Jeff Janes <jeff.janes@gmail.com>
2012-09-16 22:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-26 15:52 ` Bruce Momjian <bruce@momjian.us>
2012-09-18 07:28 ` Bill Martin <bill.martin@communote.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox