Обсуждение: Ugh - bad plan with LIMIT in a complex SELECT, any way to fix this?

I have a message posted in pgsql-general that outlines what I thought
was an indexing problem - it's not, so I'm bringing it here.

I dumped the table from our production system and stuffed it into a test
machine, then started refining and playing with the query until I was
able to get it to the "de-minimus" that showed the issue.  Note that the
actual query is frequently MUCH more complicated, but without the LIMIT
shown below the planner seems to do a decent job of figuring out how to
"get it done."

The actual table in question has ~2m rows totaling several gigabytes of
space.

Here's an abstract of the schema:

                                      Table "public.post"
  Column   |           Type           |
Modifiers
-----------+--------------------------+--------------------------------------------------------
subject   | text                     |
 message   | text                     |
 inserted  | timestamp with time zone |
 modified  | timestamp with time zone |
 replied   | timestamp with time zone |
 ordinal   | integer                  | not null default
nextval('post_ordinal_seq'::regclass)

Indexes:
    "post_pkey" PRIMARY KEY, btree (ordinal)
    "idx_message" gin (to_tsvector('english'::text, message))
    "idx_subject" gin (to_tsvector('english'::text, subject))

There's a bunch of other stuff in the table and many more indices, plus
foreign references, but stripping the table down to JUST THIS shows the
problem.

ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('violence') order by modified desc;
                                                            QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=31795.16..31819.68 rows=9808 width=436) (actual
time=14.222..17.213 rows=3421 loops=1)
   Sort Key: modified
   Sort Method:  quicksort  Memory: 3358kB
   ->  Bitmap Heap Scan on post  (cost=1418.95..31144.90 rows=9808
width=436) (actual time=1.878..7.514 rows=3421 loops=1)
         Recheck Cond: (to_tsvector('english'::text, message) @@
to_tsquery('violence'::text))
         ->  Bitmap Index Scan on idx_message  (cost=0.00..1416.49
rows=9808 width=0) (actual time=1.334..1.334 rows=3434 loops=1)
               Index Cond: (to_tsvector('english'::text, message) @@
to_tsquery('violence'::text))
 Total runtime: 20.547 ms
(8 rows)

Ok, very nice.  20ms.  I like that.

Now lets limit the return to 100 items:

ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('violence') order by modified desc limit 100;

QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5348.69 rows=100 width=436) (actual
time=198.047..2607.077 rows=100 loops=1)
   ->  Index Scan Backward using post_modified on post
(cost=0.00..524599.31 rows=9808 width=436) (actual
time=198.043..2606.864 rows=100 loops=1)
         Filter: (to_tsvector('english'::text, message) @@
to_tsquery('violence'::text))
 Total runtime: 2607.231 ms
(4 rows)

Bad.  Notice that the optimizer decided it was going to do an index scan
with an internal filter on it!  That's BACKWARD; what I want is for the
planner to first execute the index scan on the GIN index, then order the
return and limit the returned data set.

But it gets much worse - let's use something that's NOT in the message
base (the table in question has some ~2m rows by the way and consumes
several gigabytes on disk - anything that actually READS the table is
instant "bad news!")


ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('hosehead') order by modified;
                                                           QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=31795.16..31819.68 rows=9808 width=436) (actual
time=0.407..0.407 rows=0 loops=1)
   Sort Key: modified
   Sort Method:  quicksort  Memory: 25kB
   ->  Bitmap Heap Scan on post  (cost=1418.95..31144.90 rows=9808
width=436) (actual time=0.402..0.402 rows=0 loops=1)
         Recheck Cond: (to_tsvector('english'::text, message) @@
to_tsquery('hosehead'::text))
         ->  Bitmap Index Scan on idx_message  (cost=0.00..1416.49
rows=9808 width=0) (actual time=0.399..0.399 rows=0 loops=1)
               Index Cond: (to_tsvector('english'::text, message) @@
to_tsquery('hosehead'::text))
 Total runtime: 0.441 ms
(8 rows)


Very fast, as you'd expect - it returned nothing.  Now let's try it with
a "LIMIT":

ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('hosehead') order by modified limit 100;
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
                                                                  QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5348.69 rows=100 width=436) (actual
time=254217.850..254217.850 rows=0 loops=1)
   ->  Index Scan using post_modified on post  (cost=0.00..524599.31
rows=9808 width=436) (actual time=254217.847..254217.847 rows=0 loops=1)
         Filter: (to_tsvector('english'::text, message) @@
to_tsquery('hosehead'::text))
 Total runtime: 254217.891 ms
(4 rows)

ticker=#

Oh crap.  It actually went through and looked at the entire freaking
table - one message at a time.

An attempt to re-write the query into something that FORCES the planner
to do the right thing fails too.  For example:

ticker=# explain analyze select * from post where ordinal in (select
ordinal from post where to_tsvector('english', message) @@
to_tsquery('hosehead')) order by modified;
                                                                 QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=94886.44..94910.96 rows=9808 width=436) (actual
time=0.884..0.884 rows=0 loops=1)
   Sort Key: public.post.modified
   Sort Method:  quicksort  Memory: 25kB
   ->  Nested Loop  (cost=31173.42..94236.19 rows=9808 width=436)
(actual time=0.879..0.879 rows=0 loops=1)
         ->  HashAggregate  (cost=31173.42..31271.50 rows=9808 width=4)
(actual time=0.877..0.877 rows=0 loops=1)
               ->  Bitmap Heap Scan on post  (cost=1422.95..31148.90
rows=9808 width=4) (actual time=0.850..0.850 rows=0 loops=1)
                     Recheck Cond: (to_tsvector('english'::text,
message) @@ to_tsquery('hosehead'::text))
                     ->  Bitmap Index Scan on idx_message
(cost=0.00..1420.50 rows=9808 width=0) (actual time=0.848..0.848 rows=0
loops=1)
                           Index Cond: (to_tsvector('english'::text,
message) @@ to_tsquery('hosehead'::text))
         ->  Index Scan using post_ordinal on post  (cost=0.00..6.41
rows=1 width=436) (never executed)
               Index Cond: (public.post.ordinal = public.post.ordinal)
 Total runtime: 0.985 ms
(12 rows)

Fast, if convoluted.


ticker=# explain analyze select * from post where ordinal in (select
ordinal from post where to_tsvector('english', message) @@
to_tsquery('hosehead')) order by modified limit 100;
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.

QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..19892.88 rows=100 width=436) (actual
time=270563.091..270563.091 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=0.00..1951093.77 rows=9808
width=436) (actual time=270563.088..270563.088 rows=0 loops=1)
         ->  Index Scan using post_modified on post
(cost=0.00..509887.63 rows=1961557 width=436) (actual
time=0.015..3427.627 rows=1953674 loops=1)
         ->  Index Scan using post_ordinal on post  (cost=0.00..0.73
rows=1 width=4) (actual time=0.134..0.134 rows=0 loops=1953674)
               Index Cond: (public.post.ordinal = public.post.ordinal)
               Filter: (to_tsvector('english'::text,
public.post.message) @@ to_tsquery('hosehead'::text))
 Total runtime: 270563.147 ms
(7 rows)

ticker=#

Ok, that didn't work either.

Interestingly enough, if I crank up the limit to 500, it starts behaving!

ticker=# explain analyze select * from post where ordinal in (select
ordinal from post where to_tsvector('english', message) @@
to_tsquery('hosehead')) order by modified limit 500;

QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=94724.91..94726.16 rows=500 width=436) (actual
time=1.475..1.475 rows=0 loops=1)
   ->  Sort  (cost=94724.91..94749.43 rows=9808 width=436) (actual
time=1.473..1.473 rows=0 loops=1)
         Sort Key: public.post.modified
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop  (cost=31173.43..94236.19 rows=9808 width=436)
(actual time=1.468..1.468 rows=0 loops=1)
               ->  HashAggregate  (cost=31173.43..31271.51 rows=9808
width=4) (actual time=1.466..1.466 rows=0 loops=1)
                     ->  Bitmap Heap Scan on post
(cost=1422.95..31148.91 rows=9808 width=4) (actual time=1.440..1.440
rows=0 loops=1)
                           Recheck Cond: (to_tsvector('english'::text,
message) @@ to_tsquery('hosehead'::text))
                           ->  Bitmap Index Scan on idx_message
(cost=0.00..1420.50 rows=9808 width=0) (actual time=1.438..1.438 rows=0
loops=1)
                                 Index Cond:
(to_tsvector('english'::text, message) @@ to_tsquery('hosehead'::text))
               ->  Index Scan using post_ordinal on post
(cost=0.00..6.41 rows=1 width=436) (never executed)
                     Index Cond: (public.post.ordinal = public.post.ordinal)
 Total runtime: 1.600 ms
(13 rows)

Why is the planner "taking into consideration" the LIMIT (I know the
docs say it does) and choosing to sequentially scan a table of nearly 2
million rows?!  I don't see how that makes sense.... irrespective of the
query being LIMITed.

If it matters setting enable_seqscan OFF does not impact the results.

-- Karl

Overal comment.. Try reading hrough these old threads most of your
problem is the same issue:

http://article.gmane.org/gmane.comp.db.postgresql.performance/22395/match=gin
http://thread.gmane.org/gmane.comp.db.postgresql.performance/22331/focus=22434


>                                        Table "public.post"
>    Column   |           Type           |
> Modifiers
> -----------+--------------------------+--------------------------------------------------------
> subject   | text                     |
>   message   | text                     |
>   inserted  | timestamp with time zone |
>   modified  | timestamp with time zone |
>   replied   | timestamp with time zone |
>   ordinal   | integer                  | not null default
> nextval('post_ordinal_seq'::regclass)
>
> Indexes:
>      "post_pkey" PRIMARY KEY, btree (ordinal)
>      "idx_message" gin (to_tsvector('english'::text, message))
>      "idx_subject" gin (to_tsvector('english'::text, subject))
>
> There's a bunch of other stuff in the table and many more indices, plus
> foreign references, but stripping the table down to JUST THIS shows the
> problem.
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('violence') order by modified desc;
>                                                              QUERY
> PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------
>   Sort  (cost=31795.16..31819.68 rows=9808 width=436) (actual
> time=14.222..17.213 rows=3421 loops=1)
>     Sort Key: modified
>     Sort Method:  quicksort  Memory: 3358kB
>     ->   Bitmap Heap Scan on post  (cost=1418.95..31144.90 rows=9808
> width=436) (actual time=1.878..7.514 rows=3421 loops=1)
>           Recheck Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
>           ->   Bitmap Index Scan on idx_message  (cost=0.00..1416.49
> rows=9808 width=0) (actual time=1.334..1.334 rows=3434 loops=1)
>                 Index Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
>   Total runtime: 20.547 ms
> (8 rows)
>
> Ok, very nice.  20ms.  I like that.
>
> Now lets limit the return to 100 items:
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('violence') order by modified desc limit 100;
>
> QUERY
> PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=0.00..5348.69 rows=100 width=436) (actual
> time=198.047..2607.077 rows=100 loops=1)
>     ->   Index Scan Backward using post_modified on post
> (cost=0.00..524599.31 rows=9808 width=436) (actual
> time=198.043..2606.864 rows=100 loops=1)
>           Filter: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
>   Total runtime: 2607.231 ms
> (4 rows)
>
> Bad.  Notice that the optimizer decided it was going to do an index scan
> with an internal filter on it!  That's BACKWARD; what I want is for the
> planner to first execute the index scan on the GIN index, then order the
> return and limit the returned data set.
>
> But it gets much worse - let's use something that's NOT in the message
> base (the table in question has some ~2m rows by the way and consumes
> several gigabytes on disk - anything that actually READS the table is
> instant "bad news!")
>

The one problem is that the query-planner doesn't have any
specific knowlege about the cost of the gin-index search. Thats
mentioned in one of the above threads.

The other problem is that the cost of "to_tsvector" and "ts_match_vq"
are set way to conservative in the default installation. Bumping those
up will increase your amount of correct plans, but it doesnt solve all
of it
since the above problem is also interferring. But try upping the cost
of those two functions significantly.

alter function ts_match_vq(tsvector,tsquery) cost 500
(upping the cost times 500 for that one). I've I've got it right it is "more in
the correct ballpark" it more or less translates to "how much more expensive the function
is compared to really simple operators").

Another thing you can do, that favours the running time of the queries
using to_tsvector() is to specifically store the tsvector in the table and
create an index on that. That will at run-time translate into fewer
calls (0 to be precise) of to_tsvector and only costing the ts_match_vq
at run-time.

> Why is the planner "taking into consideration" the LIMIT (I know the
> docs say it does) and choosing to sequentially scan a table of nearly 2
> million rows?!  I don't see how that makes sense.... irrespective of the
> query being LIMITed.
>
> If it matters setting enable_seqscan OFF does not impact the results.
>

No, because you end up in index-scans on non-gin indexes in that
situtaion.. so turning seqscan off has no effect.


--
Jesper

>
>
>

Karl,

estimation for gin indexes currently is very-very bad, so I don't surprise
your result. We'll discuss on pgcon our new ginconstetimate function,
which could improve planner. Can you provide us dump of your db (with
relevant columns) and test queries, so we could check our new patch.

Oleg
On Sat, 8 May 2010, Karl Denninger wrote:

> I have a message posted in pgsql-general that outlines what I thought
> was an indexing problem - it's not, so I'm bringing it here.
>
> I dumped the table from our production system and stuffed it into a test
> machine, then started refining and playing with the query until I was
> able to get it to the "de-minimus" that showed the issue.  Note that the
> actual query is frequently MUCH more complicated, but without the LIMIT
> shown below the planner seems to do a decent job of figuring out how to
> "get it done."
>
> The actual table in question has ~2m rows totaling several gigabytes of
> space.
>
> Here's an abstract of the schema:
>
>                                      Table "public.post"
>  Column   |           Type           |
> Modifiers
> -----------+--------------------------+--------------------------------------------------------
> subject   | text                     |
> message   | text                     |
> inserted  | timestamp with time zone |
> modified  | timestamp with time zone |
> replied   | timestamp with time zone |
> ordinal   | integer                  | not null default
> nextval('post_ordinal_seq'::regclass)
>
> Indexes:
>    "post_pkey" PRIMARY KEY, btree (ordinal)
>    "idx_message" gin (to_tsvector('english'::text, message))
>    "idx_subject" gin (to_tsvector('english'::text, subject))
>
> There's a bunch of other stuff in the table and many more indices, plus
> foreign references, but stripping the table down to JUST THIS shows the
> problem.
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('violence') order by modified desc;
>                                                            QUERY
> PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------
> Sort  (cost=31795.16..31819.68 rows=9808 width=436) (actual
> time=14.222..17.213 rows=3421 loops=1)
>   Sort Key: modified
>   Sort Method:  quicksort  Memory: 3358kB
>   ->  Bitmap Heap Scan on post  (cost=1418.95..31144.90 rows=9808
> width=436) (actual time=1.878..7.514 rows=3421 loops=1)
>         Recheck Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
>         ->  Bitmap Index Scan on idx_message  (cost=0.00..1416.49
> rows=9808 width=0) (actual time=1.334..1.334 rows=3434 loops=1)
>               Index Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
> Total runtime: 20.547 ms
> (8 rows)
>
> Ok, very nice.  20ms.  I like that.
>
> Now lets limit the return to 100 items:
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('violence') order by modified desc limit 100;
>
> QUERY
> PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.00..5348.69 rows=100 width=436) (actual
> time=198.047..2607.077 rows=100 loops=1)
>   ->  Index Scan Backward using post_modified on post
> (cost=0.00..524599.31 rows=9808 width=436) (actual
> time=198.043..2606.864 rows=100 loops=1)
>         Filter: (to_tsvector('english'::text, message) @@
> to_tsquery('violence'::text))
> Total runtime: 2607.231 ms
> (4 rows)
>
> Bad.  Notice that the optimizer decided it was going to do an index scan
> with an internal filter on it!  That's BACKWARD; what I want is for the
> planner to first execute the index scan on the GIN index, then order the
> return and limit the returned data set.
>
> But it gets much worse - let's use something that's NOT in the message
> base (the table in question has some ~2m rows by the way and consumes
> several gigabytes on disk - anything that actually READS the table is
> instant "bad news!")
>
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('hosehead') order by modified;
>                                                           QUERY
> PLAN
>
--------------------------------------------------------------------------------------------------------------------------------
> Sort  (cost=31795.16..31819.68 rows=9808 width=436) (actual
> time=0.407..0.407 rows=0 loops=1)
>   Sort Key: modified
>   Sort Method:  quicksort  Memory: 25kB
>   ->  Bitmap Heap Scan on post  (cost=1418.95..31144.90 rows=9808
> width=436) (actual time=0.402..0.402 rows=0 loops=1)
>         Recheck Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('hosehead'::text))
>         ->  Bitmap Index Scan on idx_message  (cost=0.00..1416.49
> rows=9808 width=0) (actual time=0.399..0.399 rows=0 loops=1)
>               Index Cond: (to_tsvector('english'::text, message) @@
> to_tsquery('hosehead'::text))
> Total runtime: 0.441 ms
> (8 rows)
>
>
> Very fast, as you'd expect - it returned nothing.  Now let's try it with
> a "LIMIT":
>
> ticker=# explain analyze select * from post where to_tsvector('english',
> message) @@ to_tsquery('hosehead') order by modified limit 100;
> NOTICE:  word is too long to be indexed
> DETAIL:  Words longer than 2047 characters are ignored.
>                                                                  QUERY
> PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.00..5348.69 rows=100 width=436) (actual
> time=254217.850..254217.850 rows=0 loops=1)
>   ->  Index Scan using post_modified on post  (cost=0.00..524599.31
> rows=9808 width=436) (actual time=254217.847..254217.847 rows=0 loops=1)
>         Filter: (to_tsvector('english'::text, message) @@
> to_tsquery('hosehead'::text))
> Total runtime: 254217.891 ms
> (4 rows)
>
> ticker=#
>
> Oh crap.  It actually went through and looked at the entire freaking
> table - one message at a time.
>
> An attempt to re-write the query into something that FORCES the planner
> to do the right thing fails too.  For example:
>
> ticker=# explain analyze select * from post where ordinal in (select
> ordinal from post where to_tsvector('english', message) @@
> to_tsquery('hosehead')) order by modified;
>                                                                 QUERY
> PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------
> Sort  (cost=94886.44..94910.96 rows=9808 width=436) (actual
> time=0.884..0.884 rows=0 loops=1)
>   Sort Key: public.post.modified
>   Sort Method:  quicksort  Memory: 25kB
>   ->  Nested Loop  (cost=31173.42..94236.19 rows=9808 width=436)
> (actual time=0.879..0.879 rows=0 loops=1)
>         ->  HashAggregate  (cost=31173.42..31271.50 rows=9808 width=4)
> (actual time=0.877..0.877 rows=0 loops=1)
>               ->  Bitmap Heap Scan on post  (cost=1422.95..31148.90
> rows=9808 width=4) (actual time=0.850..0.850 rows=0 loops=1)
>                     Recheck Cond: (to_tsvector('english'::text,
> message) @@ to_tsquery('hosehead'::text))
>                     ->  Bitmap Index Scan on idx_message
> (cost=0.00..1420.50 rows=9808 width=0) (actual time=0.848..0.848 rows=0
> loops=1)
>                           Index Cond: (to_tsvector('english'::text,
> message) @@ to_tsquery('hosehead'::text))
>         ->  Index Scan using post_ordinal on post  (cost=0.00..6.41
> rows=1 width=436) (never executed)
>               Index Cond: (public.post.ordinal = public.post.ordinal)
> Total runtime: 0.985 ms
> (12 rows)
>
> Fast, if convoluted.
>
>
> ticker=# explain analyze select * from post where ordinal in (select
> ordinal from post where to_tsvector('english', message) @@
> to_tsquery('hosehead')) order by modified limit 100;
> NOTICE:  word is too long to be indexed
> DETAIL:  Words longer than 2047 characters are ignored.
>
> QUERY
> PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.00..19892.88 rows=100 width=436) (actual
> time=270563.091..270563.091 rows=0 loops=1)
>   ->  Nested Loop Semi Join  (cost=0.00..1951093.77 rows=9808
> width=436) (actual time=270563.088..270563.088 rows=0 loops=1)
>         ->  Index Scan using post_modified on post
> (cost=0.00..509887.63 rows=1961557 width=436) (actual
> time=0.015..3427.627 rows=1953674 loops=1)
>         ->  Index Scan using post_ordinal on post  (cost=0.00..0.73
> rows=1 width=4) (actual time=0.134..0.134 rows=0 loops=1953674)
>               Index Cond: (public.post.ordinal = public.post.ordinal)
>               Filter: (to_tsvector('english'::text,
> public.post.message) @@ to_tsquery('hosehead'::text))
> Total runtime: 270563.147 ms
> (7 rows)
>
> ticker=#
>
> Ok, that didn't work either.
>
> Interestingly enough, if I crank up the limit to 500, it starts behaving!
>
> ticker=# explain analyze select * from post where ordinal in (select
> ordinal from post where to_tsvector('english', message) @@
> to_tsquery('hosehead')) order by modified limit 500;
>
> QUERY
> PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=94724.91..94726.16 rows=500 width=436) (actual
> time=1.475..1.475 rows=0 loops=1)
>   ->  Sort  (cost=94724.91..94749.43 rows=9808 width=436) (actual
> time=1.473..1.473 rows=0 loops=1)
>         Sort Key: public.post.modified
>         Sort Method:  quicksort  Memory: 25kB
>         ->  Nested Loop  (cost=31173.43..94236.19 rows=9808 width=436)
> (actual time=1.468..1.468 rows=0 loops=1)
>               ->  HashAggregate  (cost=31173.43..31271.51 rows=9808
> width=4) (actual time=1.466..1.466 rows=0 loops=1)
>                     ->  Bitmap Heap Scan on post
> (cost=1422.95..31148.91 rows=9808 width=4) (actual time=1.440..1.440
> rows=0 loops=1)
>                           Recheck Cond: (to_tsvector('english'::text,
> message) @@ to_tsquery('hosehead'::text))
>                           ->  Bitmap Index Scan on idx_message
> (cost=0.00..1420.50 rows=9808 width=0) (actual time=1.438..1.438 rows=0
> loops=1)
>                                 Index Cond:
> (to_tsvector('english'::text, message) @@ to_tsquery('hosehead'::text))
>               ->  Index Scan using post_ordinal on post
> (cost=0.00..6.41 rows=1 width=436) (never executed)
>                     Index Cond: (public.post.ordinal = public.post.ordinal)
> Total runtime: 1.600 ms
> (13 rows)
>
> Why is the planner "taking into consideration" the LIMIT (I know the
> docs say it does) and choosing to sequentially scan a table of nearly 2
> million rows?!  I don't see how that makes sense.... irrespective of the
> query being LIMITed.
>
> If it matters setting enable_seqscan OFF does not impact the results.
>
> -- Karl
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83