Why is there an entry in the index for a row if the row is not valid?
Wouldn't it be better for the index entry validity to track the row validity.
If a particular data value for a query (join, where etc.) can be satisfied
by the index entry itself this would be a big performance gain.
For SELEC
I can understand the performance loss on non-selects for keeping the
index validity state tracking the row validity, but would that outweigh the
performance gains on selects? Depends on your mix of selects to non
selects I guess, but other database systems seem to imply that keeping
the index o
I can understand the performance loss on non-selects for keeping the
index validity state tracking the row validity, but would that outweigh the
performance gains on selects? Depends on your mix of selects to non
selects I guess, but other database systems seem to imply that keeping
the index o
On Wed, 28 Apr 2004 07:35:41 +0100, "Gary Doades" <[EMAIL PROTECTED]>
wrote:
>Why is there an entry in the index for a row if the row is not valid?
Because whether a row is seen as valid or not lies in the eye of the
transaction looking at it. Full visibility information is stored in the
heap tu
these two queries are not equal. Query1 returns 6 rows, query2 returns 0
rows, because '~*' and '=' operators are not same. BTW when you use '=',
it could use index on "item.description".
On query1, "Seq Scan on item" estimates 1 row, on query2 it estimates
733 rows. IMHO that's why query1 uses
On Tue, 27 Apr 2004 18:01:34 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote:
>On Tue, 2004-04-27 at 17:27, Vitaly Belman wrote:
>> Hello pgsql-performance,
>>
>> I discussed the whole subject for some time in DevShed and didn't
>> achieve much (as for results). I wonder if any of you guys can hel
> Rod, he has a hierarchy of genres. Genre 1 has 6379 child genres and a
> book can be in more than one of these.
bookgenres.genre_id = genre_children.genre_child_id AND
genre_children.genre_id = 1
I see, sorry. I didn't notice the genre_child_id in the where clause.
First glance had t
Silke Trissl <[EMAIL PROTECTED]> writes:
> I found out, that the change of the operator from '~*' to '=' for the
> item.description brought a great boost in performance (425 secs to 1
> sec!), but not in cost (Query plans at the end).
The main problem seems to be bad estimation of the number of
Manfred Koizar <[EMAIL PROTECTED]> writes:
> On Wed, 28 Apr 2004 07:35:41 +0100, "Gary Doades" <[EMAIL PROTECTED]>
> wrote:
>> Why is there an entry in the index for a row if the row is not valid?
> Because whether a row is seen as valid or not lies in the eye of the
> transaction looking at it.
On Mon, 26 Apr 2004, Stephan Szabo wrote:
>
> On Fri, 23 Apr 2004 [EMAIL PROTECTED] wrote:
>
> > I have a query which I think should be using an index all of the time but
> > postgres only uses the index part of the time. The index
> > (ticket_crm_map_crm_id_suppid) has the where clause column
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> There are tons of hints that it works this way in how they're written, but
> nothing that just comes out and says that with pgsql's mvcc
> implementation, an index scan still has to hit the pages that contain the
> tuples, so often in pgsql a seq sca
On Wed, 28 Apr 2004, Jie Liang wrote:
> All,
> After I upgraded postgres from 7.3.4 to 7.4.2, one of my program got following error:
> DRROR:out of memory
> DETAIL: Fail on request of size 92.
>
> any idea??
> does memory management have big difference between 7.3.4 and 7.4.2???
> t
When grilled further on (Wed, 21 Apr 2004 10:29:43 -0700),
Josh Berkus <[EMAIL PROTECTED]> confessed:
> Dave,
>
> > After some testing if you use the current head code for s_lock.c which
> > has some mods in it to alleviate this situation, and change
> > SPINS_PER_DELAY to 10 you can drastically
do you mean that, declaring an index serial, I'd never have to deal with
incrementing its primary key? good to know!
anyway in this particular situation I don't need such accurate
behaviour: this table is filled up with a lot of data twice per week and
it's used only to answer queries.
I could
do you mean that, declaring an index serial, I'd never have to deal with
incrementing its primary key? good to know!
Yep. You can use 'DEFAULT' as the value, eg:
INSERT INTO blah (DEFAULT, ...);
anyway in this particular situation I don't need such accurate
behaviour: this table is filled up wit
On Wed, Apr 28, 2004 at 10:13:14 +0200,
Edoardo Ceccarelli <[EMAIL PROTECTED]> wrote:
> do you mean that, declaring an index serial, I'd never have to deal with
> incrementing its primary key? good to know!
That isn't what is happening. Serial is a special type. It is int plus
a default rule li
16 matches
Mail list logo
