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
On Mon, Feb 28, 2011 at 02:04:53PM -0500, Robert Haas wrote:
> On Sun, Feb 27, 2011 at 1:39 PM, Tom Lane wrote:
> > Robert Haas writes:
> >> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus wrote:
> >>> I'm not saying that PostgreSQL couldn't do better on this kind of case,
> >>> but that doing bette
On 25/07/11 02:06, Дмитрий Васильев wrote:
I have a problem with poor query plan.
My PostgreSQL is "PostgreSQL 8.4.8, compiled by Visual C++ build 1400,
32-bit" installed by EnterpriseDB installer on Windows 7 32 bit.
Steps to reproduce:
Start with fresh installation and execute the following:
=?KOI8-R?B?5M3J1NLJyiD3wdPJzNjF1w==?= writes:
> explain analyze insert into large(id) select id from small where id
> not in(select id from large);
> [ crummy plan ]
> explain analyze insert into large(id) select id from small where not
> exists (select id from large l where small.id=l.id);
> [ be
I have a problem with poor query plan.
My PostgreSQL is "PostgreSQL 8.4.8, compiled by Visual C++ build 1400,
32-bit" installed by EnterpriseDB installer on Windows 7 32 bit.
Steps to reproduce:
Start with fresh installation and execute the following:
drop table if exists small;
drop table if e
Whoops,
I meant this query (ordering my messageid):
SELECT messageID FROM Message WHERE modificationDate>= 1302627793988 ORDER BY
messageID LIMIT 1;
Sometimes this gives the better plan. But not always.
On 04/15/2011 11:13 AM, Kevin Grittner wrote:
Mark Williams wrote:
If I re-write th
Mark Williams wrote:
> If I re-write the query like this:
>
> explain SELECT messageID FROM Message WHERE modificationDate >=
> 1302627793988 ORDER BY modificationDate LIMIT 1;
> I also get a better plan.
Yeah, but it's not necessarily the same value. Do you want the
minimum messageID whe
Thanks for the response guys. There is something else which confuses me.
If I re-write the query like this:
explain SELECT messageID FROM Message WHERE modificationDate >=
1302627793988 ORDER BY modificationDate LIMIT 1;
QUERY PLAN
---
Mark Williams wrote:
> explain SELECT min(messageID) FROM Message
> WHERE modificationDate >= 1302627793988;
> For some reason it is deciding to scan the primary key column of
> the table. This results in scanning the entire table
No, it scans until it finds the first row where modificatio
On Fri, Apr 15, 2011 at 10:17:32AM -0700, Mark Williams wrote:
> We are experiencing a problem with our query plans when using a range query
> in Postgresql 8.3. The query we are executing attempts to select the
> minimum primary key id after a certain date. Our date columns are bigint's
> holdi
We are experiencing a problem with our query plans when using a range
query in Postgresql 8.3. The query we are executing attempts to select
the minimum primary key id after a certain date. Our date columns are
bigint's holding a unix epoch representation of the date. We have an
index on the pr
On Sun, Feb 27, 2011 at 1:39 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus wrote:
>>> I'm not saying that PostgreSQL couldn't do better on this kind of case,
>>> but that doing better is a major project, not a minor one.
>
>> Specifically, the problem i
Robert Haas writes:
> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus wrote:
>> I'm not saying that PostgreSQL couldn't do better on this kind of case,
>> but that doing better is a major project, not a minor one.
> Specifically, the problem is that x = 4.0, where x is an integer, is
> defined to me
On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus wrote:
> Laszlo,
>
>> Which is silly. I think that PostgreSQL converts the int side to a
>> float, and then compares them.
>>
>> It would be better to do this, for each item in the loop:
>>
>> * evaluate the right side (which is float)
>> * tell
09.02.11 01:14, Dave Crooke написав(ла):
You will get the same behaviour from any database product where the
query as written requires type coercion - the coercion has to go in
the direction of the "wider" type. I have seen the exact same scenario
with Oracle, and I view it as a problem with th
You will get the same behaviour from any database product where the query as
written requires type coercion - the coercion has to go in the direction of
the "wider" type. I have seen the exact same scenario with Oracle, and I
view it as a problem with the way the query is written, not with the
data
Laszlo,
> Which is silly. I think that PostgreSQL converts the int side to a
> float, and then compares them.
>
> It would be better to do this, for each item in the loop:
>
> * evaluate the right side (which is float)
> * tell if it is an integer or not
> * if not an integer, then d
This query:
select p.id,p.producer_id,visa.variation_item_id, vi.qtyavail
from variation_item_sellingsite_asin visa
inner join product p on p.id = visa.product_id
inner join variation_item vi on vi.id =
visa.var
Tom Lane a écrit :
Benoit Delbosc writes:
I am trying to understand why inside an EXISTS clause the query planner
does not use the index:
I'm not sure this plan is as bad as all that. The key point is that the
planner is expecting 52517 rows that match that users_md5 value (and the
true nu
Benoit Delbosc writes:
> I am trying to understand why inside an EXISTS clause the query planner
> does not use the index:
I'm not sure this plan is as bad as all that. The key point is that the
planner is expecting 52517 rows that match that users_md5 value (and the
true number is evidently
Yeb Havinga a écrit :
Yeb Havinga wrote:
Kenneth Marshall wrote:
EXISTS matches NULLs too and since they are not indexed a
sequential scan is needed to check for them. Try using
IN instead.
This is nonsense in more than one way.
Hit ctrl-return a bit too slow - exists does not match null bu
Yeb Havinga wrote:
Kenneth Marshall wrote:
EXISTS matches NULLs too and since they are not indexed a
sequential scan is needed to check for them. Try using
IN instead.
This is nonsense in more than one way.
Hit ctrl-return a bit too slow - exists does not match null but a set of
records, th
Kenneth Marshall wrote:
EXISTS matches NULLs too and since they are not indexed a
sequential scan is needed to check for them. Try using
IN instead.
This is nonsense in more than one way.
regards
Yeb Havinga
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To m
try JOINs...
EXISTS matches NULLs too and since they are not indexed a
sequential scan is needed to check for them. Try using
IN instead.
Cheers,
Ken
On Wed, Mar 10, 2010 at 02:26:20PM +0100, Benoit Delbosc wrote:
> Hi all,
>
> I am trying to understand why inside an EXISTS clause the query planner
> does n
Hi all,
I am trying to understand why inside an EXISTS clause the query planner
does not use the index:
EXPLAIN ANALYZE SELECT 1 WHERE EXISTS (SELECT 1 FROM read_acls_cache
WHERE users_md5 = '9bc9012eb29c0bb2ae3cc7b5e78c2acf');
QUERY PLAN
33 matches
Mail list logo
