On 22 March 2014 16:28, Jim Nasby wrote:
> On 3/21/14, 7:59 PM, Vik Fearing wrote:
>>
>> On 03/22/2014 01:43 AM, Thom Brown wrote:
>>>
>>> Hi,
>>>
>>> I've created a table with 1000 partial indexes. Each one matches
>>> exactly one row based on the predicate WHERE id = .
>>>
>>> However, when I p
On 3/21/14, 7:59 PM, Vik Fearing wrote:
On 03/22/2014 01:43 AM, Thom Brown wrote:
Hi,
I've created a table with 1000 partial indexes. Each one matches
exactly one row based on the predicate WHERE id = .
However, when I perform an UPDATE of a single row in a transaction,
I've noticed that all
On 22 March 2014 15:04, Tom Lane wrote:
> Thom Brown writes:
>> On 22 March 2014 05:32, Tom Lane wrote:
>>> Yes. You can't determine whether the index needs to get a new entry
>>> without examining its metadata, and that's what the lock is mainly about.
>
>> I see. Why does this apply to delet
Thom Brown writes:
> On 22 March 2014 05:32, Tom Lane wrote:
>> Yes. You can't determine whether the index needs to get a new entry
>> without examining its metadata, and that's what the lock is mainly about.
> I see. Why does this apply to deletes too?
The executor doesn't take locks on inde
On 22 March 2014 05:32, Tom Lane wrote:
> Thom Brown writes:
>> Is it necessary for a partial index that doesn't include the row to be
>> involved in locking?
>
> Yes. You can't determine whether the index needs to get a new entry
> without examining its metadata, and that's what the lock is mai
Thom Brown writes:
> Is it necessary for a partial index that doesn't include the row to be
> involved in locking?
Yes. You can't determine whether the index needs to get a new entry
without examining its metadata, and that's what the lock is mainly about.
The only possible alternative would be
On 22 March 2014 00:59, Vik Fearing wrote:
> On 03/22/2014 01:43 AM, Thom Brown wrote:
>> Hi,
>>
>> I've created a table with 1000 partial indexes. Each one matches
>> exactly one row based on the predicate WHERE id = .
>>
>> However, when I perform an UPDATE of a single row in a transaction,
>>
On 03/22/2014 01:43 AM, Thom Brown wrote:
> Hi,
>
> I've created a table with 1000 partial indexes. Each one matches
> exactly one row based on the predicate WHERE id = .
>
> However, when I perform an UPDATE of a single row in a transaction,
> I've noticed that all those partial indexes show up i
Hi,
I've created a table with 1000 partial indexes. Each one matches
exactly one row based on the predicate WHERE id = .
However, when I perform an UPDATE of a single row in a transaction,
I've noticed that all those partial indexes show up in pg_locks with
RowExclusiveLock.
Only 2 of those ind
I looked into the optimization regression reported here:
http://archives.postgresql.org/pgsql-performance/2012-11/msg00140.php
It's easy to reproduce the problem in the regression database:
create index ti on tenk1 (fivethous) where fivethous is not null;
explain select * from int4_tbl, tenk1 whe
Christopher Kings-Lynne kirjutas R, 07.03.2003 kell 07:28:
> Yeah, it's not really a problem for me, I just put the extra clause in.
>
> Is indexing excluding NULLs a common application of partial indexes?
For me it is ;)
> It's
> basically all I use it for, when a column has like 90-95% NULLS
> The optimizer does not think that "pbx_date = CURRENT_DATE" satisfies the
> partial index's WHERE condition. I don't see any really good way around
> this; to improve matters there'd need to be some concept of a plan that
> is only good for a limited time.
It's the same as the slight issue I ha
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> Once I thought of a conversion lookup cache, but it seems impossible
> to implent it since the cache needs to be invalidated when the schema
> search path is changed.
On the contrary, that seems very easy to do. There is a hook to let you
get control wh
> This sounds like a really bad idea to me. A syscache based on a partial
> index is almost certainly not going to work.
>
> Before we invest in a lot of effort making bootstrap, syscache, and who
> knows what else support partial indexes, I want to see a very clear
> explanation why we must do
Bruce Momjian <[EMAIL PROTECTED]> writes:
> In private email with Tatsuo, I learned it is for the new loadable
> encoding patch, and he wants to use the index from the syscache. The
> reason for the partial index is because the index itself would not be
> unique, but a partial index would be uniq
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> It seems bootstrap parser(bootparse.y) does not accept partial index
> definitions. Is there any reason for this?
Why should it? The boot parser need handle only a minimal set of
operations. Why is there any need to handle partial indexes there?
Tatsuo Ishii wrote:
> It seems bootstrap parser(bootparse.y) does not accept partial index
> definitions. Is there any reason for this?
In private email with Tatsuo, I learned it is for the new loadable
encoding patch, and he wants to use the index from the syscache. The
reason for the partial i
Tatsuo Ishii wrote:
> It seems bootstrap parser(bootparse.y) does not accept partial index
> definitions. Is there any reason for this?
Probably just because we never needed them. We could add it, or just
create the index later in the initdb script. That later seems easier.
--
Bruce Momjian
It seems bootstrap parser(bootparse.y) does not accept partial index
definitions. Is there any reason for this?
--
Tatsuo Ishii
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourE
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
>> Since bid is not in an index the evaluation of usability obviously
>> should not be based on index ops ?
Actually, now that I think about it, there's no reason that the prover
couldn't try a simple equal() on a WHERE clause and predicate
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
> Since bid is not in an index the evaluation of usability obviously
> should not be based on index ops ?
Feel free to reimplement the theorem-prover, taking special care to
be able to prove things about operators that you have zero informa
> > test=# create index myindex on accounts(aid) where bid <> 0;
>
> > Hmm ? Am I reading correctly ? a restriction that is on a field,
that
> > is not in the index ? Does that make sense ?
>
> Yes it does, and in fact it's one of the more important applications
of
> partial indexes. It's the
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
>> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> test=# create index myindex on accounts(aid) where bid <> 0;
> Hmm ? Am I reading correctly ? a restriction that is on a field, that
> is not in the index ? Does that make sense ?
Yes it does,
> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > test=# create index myindex on accounts(aid) where bid <> 0;
Hmm ? Am I reading correctly ? a restriction that is on a field, that
is not in the index ? Does that make sense ? (aid --> bid)
> The original implementation would have refused to let y
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> test=# create index myindex on accounts(aid) where bid <> 0;
> test=# explain select * from accounts where aid < 10 and bid <> 0;
> and I got a log message:
> DEBUG: clause_pred_clause_test: unknown pred_op
> Is this normal?
Yes. We might want to s
I have created a test data using pgbench, played with the partial
index.
test=# create index myindex on accounts(aid) where bid <> 0;
CREATE
test=# explain select * from accounts where aid < 10 and bid <> 0;
and I got a log message:
DEBUG: clause_pred_clause_test: unknown pred_op
Is this norm
26 matches
Mail list logo
