Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-18 Thread Bill Martin
Tom Lane writes: > Bill Martin writes: >> Tom Lane 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 ta

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Tom Lane
Bill Martin writes: > Tom Lane 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.

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Bill Martin
> Tom Lane 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? -- Sent via p

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Tom Lane
Jesper Krogh 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('sim

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Jesper Krogh
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

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Bill Martin
> Tom Lane writes: >> Bill Martin 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

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Tom Lane
Bill Martin 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?

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-13 Thread Bill Martin
Tom Lane writes: > Bill Martin 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

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-11 Thread Tom Lane
Bill Martin 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)

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-11 Thread Bill Martin
On 10/09/12 16:24, bill_mar...@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 exec

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-10 Thread Jesper Krogh
On 10/09/12 16:24, bill_mar...@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 g

[PERFORM] Planner selects different execution plans depending on limit

2012-09-10 Thread bill_martin
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