I have a planner problem that looks like a bug, but I'm not familiar enough
with how planner the works to say for sure.
This is my schema:
create table comments (
id serial primary key,
conversation_id integer,
created_at timestamp
);
create index comments_conversat
Alexander Staubo writes:
> That's right. So I created a composite index, and not only does this make the
> plan correct, but the planner now chooses a much more efficient plan than the
> previous index that indexed only on "conversation_id":
> ...
> Is this because it can get the value of "creat
Alexander Staubo wrote:
> On Friday, February 22, 2013 at 21:47 , Kevin Grittner wrote:
>> In my experience these problems come largely from the planner
>> not knowing the cost of dealing with each tuple. I see a lot
>> less of this if I raise cpu_tuple_cost to something in the 0.03
>> to 0.05 ra
On Friday, February 22, 2013 at 21:47 , Kevin Grittner wrote:
> I suspect you would be better off without those two indexes, and
> instead having an index on (conversation_id, created_at). Not just
> for the query you show, but in general.
Indeed, that solved it, thanks!
> In my experience th
On Friday, February 22, 2013 at 21:33 , Tom Lane wrote:
> The reason is that the LIMIT may stop the query before it's scanned all
> of the index. The planner estimates on the assumption that the desired
> rows are roughly uniformly distributed within the created_at index, and
> on that assumption,
Alexander Staubo wrote:
> This is my schema:
>
> create table comments (
> id serial primary key,
> conversation_id integer,
> created_at timestamp
> );
> create index comments_conversation_id_index on comments (conversation_id);
> create index comments_created_at_index on com
Alexander Staubo writes:
>select comments.id from comments where
> conversation_id = 3975979 order by created_at limit 13
> I'm at a loss how to explain why the planner thinks scanning a huge
> index that covers the entire table will ever beat scanning a small index
> that has 17% of the
I have a problem with a query that is planned wrong. This is my schema:
create table comments (
id serial primary key,
conversation_id integer,
created_at timestamp
);
create index comments_conversation_id_index on comments (conversation_id);
create index comments_create
