Given an index like this:
CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
and a query like this:
SELECT * FROM t1 WHERE c1 = 123;
I'd like the planner to be smart enough to use an index scan using i1. Yes,
I can change the query to this:
SELECT * FROM t1 WHERE c1 = 123 AND
Vivek Khera wrote:
> If you've got the time, could you try also doing the full bulk insert
> test with the checkpoint log files on another physical disk? See if
> that's any faster.
We have been doing that for a few weeks, but the performance
improvements are less than what we expected. There i
L.S.
Could anybody explain why the planner is doing what it is doing?
What could I do to make it easier to choose a better plan?
*
Summary
*
On a freshly vacuum/analysed pair of tables with 7389 and 64333 records, this:
select id from location where id not in (select location_
On Thu, 10 Jun 2004, Frank van Vugt wrote:
> Could anybody explain why the planner is doing what it is doing?
>
> What could I do to make it easier to choose a better plan?
You might try raising sort_mem to see if it chooses a better plan. I
think it may be guessing that the hash won't fit and
Frank van Vugt <[EMAIL PROTECTED]> writes:
> What could I do to make it easier to choose a better plan?
Increase sort_mem. You want it to pick a "hashed subplan", but
it's not doing so because 64000 rows won't fit in the default
sort_mem.
regards, tom lane
--
Wow,
The effectiveness of the pgsql mailinglists never ceases to amaze me.
Default sort mem it was, I guess I'd simply been to cautious with this
per-client setting.
Stephan & Tom : thanks!
--
Best,
Frank.
---(end of broadcast)---
TI
The real question is:
If the two statments are functionally equivalent, why can't PG rewrite
the "NOT IN" version into the more efficient "NOT EXISTS"?
Frank van Vugt wrote:
L.S.
Could anybody explain why the planner is doing what it is doing?
What could I do to make it easier to choose a better
Vivek,
Was there anything specific that helped you decide on a RAID-5 and not a RAID-10?
I have my DBs on RAID10, and would soon be moving them on FC drives, and i am
considering RAID-10.
Thanks,
Anjan
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Jean-Luc Lachance <[EMAIL PROTECTED]> writes:
> If the two statments are functionally equivalent, why can't PG rewrite
> the "NOT IN" version into the more efficient "NOT EXISTS"?
They're not equivalent. In particular, the behavior in the presence of
NULLs is quite different.
On Jun 10, 2004, at 12:02 PM, Anjan Dave wrote:
Vivek,
Was there anything specific that helped you decide on a RAID-5 and not
a RAID-10?
performance testing on restore times. My DB is more than 50% write, so
I needed to optimize for writes.
I have my DBs on RAID10, and would soon be moving them
I agree, but it should be a simple rewrite. No?
x IS NULL/IS NOT NULL AND/OR NOT EXISTS
Tom Lane wrote:
Jean-Luc Lachance <[EMAIL PROTECTED]> writes:
If the two statments are functionally equivalent, why can't PG rewrite
the "NOT IN" version into the more efficient "NOT EXISTS"?
They're not equiv
On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
> I agree, but it should be a simple rewrite. No?
It's NULLs inside the subselect that are the issue.
select 1 in (select a from foo)
select exists ( select 1 from foo where a=1)
If foo.a contains a row with NULL but no rows containing a 1, the abo
On Thu, 10 Jun 2004, Stephan Szabo wrote:
>
> On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
>
> > I agree, but it should be a simple rewrite. No?
>
> It's NULLs inside the subselect that are the issue.
>
> select 1 in (select a from foo)
> select exists ( select 1 from foo where a=1)
>
> If foo.a
13 matches
Mail list logo
