Re: [PERFORM] NULLS LAST performance

2011-03-10 Thread Robert Haas
On Thu, Mar 10, 2011 at 11:32 AM, Merlin Moncure wrote: > On Thu, Mar 10, 2011 at 9:55 AM, Robert Haas wrote: >> On Wed, Mar 9, 2011 at 6:01 PM, Jim Nasby wrote: >>> Unfortunately, I don't think the planner actually has that level of >>> knowledge. >> >> Actually, I don't think it would be that

Re: [PERFORM] NULLS LAST performance

2011-03-10 Thread Merlin Moncure
On Thu, Mar 10, 2011 at 9:55 AM, Robert Haas wrote: > On Wed, Mar 9, 2011 at 6:01 PM, Jim Nasby wrote: >> Unfortunately, I don't think the planner actually has that level of >> knowledge. > > Actually, I don't think it would be that hard to teach the planner > about that special case... > >> A m

Re: [PERFORM] NULLS LAST performance

2011-03-10 Thread Robert Haas
On Wed, Mar 9, 2011 at 6:01 PM, Jim Nasby wrote: > Unfortunately, I don't think the planner actually has that level of knowledge. Actually, I don't think it would be that hard to teach the planner about that special case... > A more reasonable fix might be to teach the executor that it can do 2

Re: [PERFORM] NULLS LAST performance

2011-03-09 Thread Jim Nasby
On Feb 24, 2011, at 3:47 AM, Mathieu De Zutter wrote: > > which will index optimize your sql. Interesting that 'null last' > > fools disallows index usage even when the index was created with > > nullls last as the default. > > The problem is that his query needs to scan the index in DESC order,

Re: [PERFORM] NULLS LAST performance

2011-02-24 Thread Mathieu De Zutter
On Wed, Feb 23, 2011 at 10:37 PM, Tom Lane wrote: > Merlin Moncure writes: > > you can always do this: > > > create index performance_creation_date_desc_idx on > > performance(creation_date desc nulls last); > > > which will index optimize your sql. Interesting that 'null last' > > fools disall

Re: [PERFORM] NULLS LAST performance

2011-02-23 Thread Tom Lane
Merlin Moncure writes: > you can always do this: > create index performance_creation_date_desc_idx on > performance(creation_date desc nulls last); > which will index optimize your sql. Interesting that 'null last' > fools disallows index usage even when the index was created with > nullls last

Re: [PERFORM] NULLS LAST performance

2011-02-23 Thread Merlin Moncure
On Wed, Feb 23, 2011 at 1:27 PM, Mathieu De Zutter wrote: > Hi all, > Running PostgreSQL 8.4.7 (backport package from Debian Lenny). > I have some queries that are based on views, and an engine adds a few > clauses (like NULLS LAST). One of these queries has a performance problem. > The simplified

[PERFORM] NULLS LAST performance

2011-02-23 Thread Mathieu De Zutter
Hi all, Running PostgreSQL 8.4.7 (backport package from Debian Lenny). I have some queries that are based on views, and an engine adds a few clauses (like NULLS LAST). One of these queries has a performance problem. The simplified form is this: shs=# explain analyze select * from performance e