Re: ctid access is slow

Hello!

Could anybody help me with this [artificial] query

select ctid from aaa where ctid in (select ctid from aaa limit 10);

here is explained plan

Nested Loop IN Join (cost=300000000.47..300325932.99 rows=10 width=6)
Join Filter: ("outer".ctid = "inner".ctid)
-> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998 width=6)
-> Materialize (cost=0.47..0.57 rows=10 width=6)
-> Subquery Scan "IN_subquery" (cost=0.00..0.46 rows=10 width=6)
-> Limit (cost=0.00..0.36 rows=10 width=6)
-> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998 width=6)

There are 1250998 records in aaa.

As you see it is pretty slow - actually this thing is faster
even if I use oid instead of ctid.
Inner query works promptly of course.

Any clue?

The original idea was to collect ctid's of records to delete
and use this info in DELETE statement (and something similar
with UPDATE), but performance is absolutely unacceptable.

Help is very very appeciated!

--
Best regards
Ilja Golshtein

On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
> Hello!
>
> Could anybody help me with this [artificial] query
>
> select ctid from aaa where ctid in (select ctid from aaa limit 10);
>
> here is explained plan
>
> Nested Loop IN Join (cost=300000000.47..300325932.99 rows=10 width=6)
> Join Filter: ("outer".ctid = "inner".ctid)
> -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998 width=6)
> -> Materialize (cost=0.47..0.57 rows=10 width=6)
> -> Subquery Scan "IN_subquery" (cost=0.00..0.46 rows=10 width=6)
> -> Limit (cost=0.00..0.36 rows=10 width=6)
> -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998
> width=6)
>
> There are 1250998 records in aaa.
>
> As you see it is pretty slow - actually this thing is faster
> even if I use oid instead of ctid.
> Inner query works promptly of course.
>
> Any clue?
>

I think using an indexed field would probably be faster for you, especially if
you have a PK on the table. Barring that, make sure you have
vacuumed/analyzed and send us explain analyze output.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Hi!

>> Could anybody help me with this [artificial] query
>>
>> select ctid from aaa where ctid in (select ctid from aaa limit 10);

[skipped]

>I think using an indexed field would probably be faster for you, especially >if you have a PK on the table.

I used to think ctid is the same as rowid in Oracle,
where rowid access is the fastest. Is it wrong?
After all, why oid is faster then ctid?

I consider using index of course. I just cannot
believe it is the best what I can do here.

>Barring that, make sure you have
>vacuumed/analyzed and send us explain analyze output.

I played with fresh database.

server version is 8.0.3, enable_tidscan is on,
looks like hints have no effect.

Thanks.

--
Best regards
Ilja Golshtein

On Tue, Aug 23, 2005 at 09:15:42AM -0400, Robert Treat wrote:
> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
> >
> > select ctid from aaa where ctid in (select ctid from aaa limit 10);
> >
> > Nested Loop IN Join (cost=300000000.47..300325932.99 rows=10 width=6)
> > Join Filter: ("outer".ctid = "inner".ctid)
> > -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998 width=6)
> > -> Materialize (cost=0.47..0.57 rows=10 width=6)
> > -> Subquery Scan "IN_subquery" (cost=0.00..0.46 rows=10 width=6)
> > -> Limit (cost=0.00..0.36 rows=10 width=6)
> > -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998
> > width=6)
> >
> > There are 1250998 records in aaa.
> >
> > As you see it is pretty slow - actually this thing is faster
> > even if I use oid instead of ctid.
> > Inner query works promptly of course.
> >
> > Any clue?
>
> I think using an indexed field would probably be faster for you, especially if
> you have a PK on the table. Barring that, make sure you have
> vacuumed/analyzed and send us explain analyze output.

Aside from that, ctid is of type tid, and its equality operator
isn't hashable. Here's an example that shows the difference between
ctid (not hashable) and oid (hashable) on a table with 100000 rows:

EXPLAIN ANALYZE SELECT ctid FROM foo WHERE ctid IN (SELECT ctid FROM foo LIMIT 10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.27..24137.27 rows=10 width=6) (actual time=0.127..12729.741 rows=10 loops=1)
Join Filter: ("outer".ctid = "inner".ctid)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=6) (actual time=0.029..951.297 rows=100000 loops=1)
-> Materialize (cost=0.27..0.37 rows=10 width=6) (actual time=0.005..0.052 rows=10 loops=100000)
-> Subquery Scan "IN_subquery" (cost=0.00..0.26 rows=10 width=6) (actual time=0.037..0.318 rows=10 loops=1)
-> Limit (cost=0.00..0.16 rows=10 width=6) (actual time=0.023..0.195 rows=10 loops=1)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=6) (actual time=0.013..0.094 rows=10 loops=1)
Total runtime: 12730.011 ms
(8 rows)

EXPLAIN ANALYZE SELECT oid FROM foo WHERE oid IN (SELECT oid FROM foo LIMIT 10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Hash IN Join (cost=0.29..2137.39 rows=10 width=4) (actual time=0.574..1477.235 rows=10 loops=1)
Hash Cond: ("outer".oid = "inner".oid)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=4) (actual time=0.016..864.519 rows=100000 loops=1)
-> Hash (cost=0.26..0.26 rows=10 width=4) (actual time=0.412..0.412 rows=0 loops=1)
-> Subquery Scan "IN_subquery" (cost=0.00..0.26 rows=10 width=4) (actual time=0.063..0.336 rows=10 loops=1)
-> Limit (cost=0.00..0.16 rows=10 width=4) (actual time=0.048..0.218 rows=10 loops=1)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=4) (actual time=0.035..0.118 rows=10 loops=1)
Total runtime: 1477.508 ms
(8 rows)

--
Michael Fuhr

Hello!

>On Tue, Aug 23, 2005 at 09:15:42AM -0400, Robert Treat wrote:
>> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
>> >
>> > select ctid from aaa where ctid in (select ctid from aaa limit 10);

>Aside from that, ctid is of type tid, and its equality operator
>isn't hashable.

It is the piece of knowledge I failed to deduce exploring
plans of queries ;(.

So I have no better solution then creating indexed
field of serial type, have I?

The only thing I am curios is ctid good for
anything from user point of view?

Thanks a lot.

--
Best regards
Ilja Golshtein

Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
>>> select ctid from aaa where ctid in (select ctid from aaa limit 10);

> Aside from that, ctid is of type tid, and its equality operator
> isn't hashable.

Nor mergejoinable, so there's not much scope for a smart join plan.

AFAIR without rereading the code, the only case that's actually fast is

WHERE ctid = constant [ OR ctid = constant ... ]

which of course is the same as

WHERE ctid IN (constant, constant, ...)

but not at all the same as "ctid IN (sub-select)".

>>> The original idea was to collect ctid's of records to delete
>>> and use this info in DELETE statement (and something similar
>>> with UPDATE), but performance is absolutely unacceptable.

Right, you can do that, but you have to actually collect the ctid's on
the client side and incorporate them literally into the later DELETE
command. This is probably a good idea anyway to be sure you are
deleting exactly the rows you saw before, and not some other ones that
happen to now match the query conditions you gave before. Be wary also
that you can't trust a ctid to be valid longer than one transaction.

regards, tom lane

On Tue, Aug 23, 2005 at 05:30:31PM +0400, Ilja Golshtein wrote:
> Hi!
>
> >> Could anybody help me with this [artificial] query
> >>
> >> select ctid from aaa where ctid in (select ctid from aaa limit 10);
>
> [skipped]
>
> >I think using an indexed field would probably be faster for you,
> >especially if you have a PK on the table.
>
> I used to think ctid is the same as rowid in Oracle,
> where rowid access is the fastest. Is it wrong?

I don't know what is rowid internally, but in Postgres, ctid _is_ the
fastest way to access a (single) tuple, because it's the physical
address. However, since the ctid datatype does not implement hashjoin
nor mergejoin, the plans are not as good when you have to access lots of
tuples.

> After all, why oid is faster then ctid?

Accessing single values by oid, even when there is an index, will be
slower than accessing single values by ctid; though in practice,
probably there won't be a measurable difference.

If you are too worried about it, you could look at what is needed to
implement hashjoin and mergejoin for ctids. I take it it isn't trivial,
or it would be done already, but I don't think it's too hard (unless
there is an implementation detail that makes it impossible).

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> If you are too worried about it, you could look at what is needed to
> implement hashjoin and mergejoin for ctids. I take it it isn't trivial,
> or it would be done already, but I don't think it's too hard (unless
> there is an implementation detail that makes it impossible).

It wouldn't be hard that I can see (just build hash and btree opclasses
for tid), but I'm pretty unclear on why bother. There's no use-case for
cross-table joins involving ctid, since you couldn't usefully store a
ctid referencing another table. The example Ilja showed was quite
artificial and should not convince anyone to expend effort on this.
Perhaps there are more convincing examples, but let's see one.

regards, tom lane

On Tue, Aug 23, 2005 at 06:02:05PM +0400, Ilja Golshtein wrote:

> The only thing I am curios is ctid good for anything from user point
> of view?

No -- it changes far too frequently for that.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Un poeta es un mundo encerrado en un hombre" (Victor Hugo)

Tom Lane wrote:
>
> It wouldn't be hard that I can see (just build hash and btree opclasses
> for tid), but I'm pretty unclear on why bother. There's no use-case for
> cross-table joins involving ctid, since you couldn't usefully store a
> ctid referencing another table. The example Ilja showed was quite
> artificial and should not convince anyone to expend effort on this.
> Perhaps there are more convincing examples, but let's see one.

Would it be useful for extremely static (read-only) data?

The largest tables in my database are read-only for many months
at a time (geospatial data which the vendor updates annually).
I've occasionally wondered if storing ctids in tables that link
to these tables rather than the traditional id column would help.

(I never really bothered, though; since normal index scans were
fast enough; and any future performance optimization will probably
cache this data in memcached instead.)

On Tuesday 23 August 2005 15:55, Alvaro Herrera wrote:
> On Tue, Aug 23, 2005 at 06:02:05PM +0400, Ilja Golshtein wrote:
> > The only thing I am curios is ctid good for anything from user point
> > of view?
>
> No -- it changes far too frequently for that.

Oh I dunno... In general I'd agree with you, but I've seen a couple of (some
would say hackey) use cases where you use the ctid to iterate over the
columns returned in a row in a plpgsql function... :-)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

""Ilja Golshtein"" <ilejn(at)yandex(dot)ru> wrote in message
news:430B2C5D(dot)000008(dot)17678(at)mfront7(dot)yandex(dot)ru(dot)(dot)(dot)
> Hello!
>
>>On Tue, Aug 23, 2005 at 09:15:42AM -0400, Robert Treat wrote:
>>> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
>>> >
>>> > select ctid from aaa where ctid in (select ctid from aaa limit 10);
>
>>Aside from that, ctid is of type tid, and its equality operator
>>isn't hashable.
>
> It is the piece of knowledge I failed to deduce exploring
> plans of queries ;(.
>
> So I have no better solution then creating indexed
> field of serial type, have I?
>
> The only thing I am curios is ctid good for
> anything from user point of view?

The ctid value can be useful in a multi user application, to check whether a
record has been changed by another user, before committing changes.
Whenever a record is updated the ctid value will be changed, so by storing
the ctid value when first fetching the record, that can be compared with the
current ctid value before doing the update.

>
> Thanks a lot.
>
> --
> Best regards
> Ilja Golshtein
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Wed, Aug 24, 2005 at 09:26:10AM +0930, Jeff Eckermann wrote:
> The ctid value can be useful in a multi user application, to check whether a
> record has been changed by another user, before committing changes.
> Whenever a record is updated the ctid value will be changed, so by storing
> the ctid value when first fetching the record, that can be compared with the
> current ctid value before doing the update.

I believe that's not necessarily true. If you select a tuple and it's
ctid and it's updated more than once with a vacuum in-between I believe
it could end up back in the same position, which would mean the same
ctid.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461

--- "Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:

> On Wed, Aug 24, 2005 at 09:26:10AM +0930, Jeff
> Eckermann wrote:
> > The ctid value can be useful in a multi user
> application, to check whether a
> > record has been changed by another user, before
> committing changes.
> > Whenever a record is updated the ctid value will
> be changed, so by storing
> > the ctid value when first fetching the record,
> that can be compared with the
> > current ctid value before doing the update.
>
> I believe that's not necessarily true. If you select
> a tuple and it's
> ctid and it's updated more than once with a vacuum
> in-between I believe
> it could end up back in the same position, which
> would mean the same
> ctid.

True. But the probability of that happening would
generally be low enough not to bother the designers of
most applications.

> --
> Jim C. Nasby, Sr. Engineering Consultant
> jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com
> 512-569-9461
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> I believe that's not necessarily true. If you select a tuple and it's
> ctid and it's updated more than once with a vacuum in-between I believe
> it could end up back in the same position, which would mean the same
> ctid.

This is the reason for the recommendation that you don't trust a TID for
longer than one transaction. If you select a row and see it has TID
such and such, and then later try to fetch/update/delete that row by
TID, the worst that can happen is that you'll not find the row because
some other xact has already updated or deleted it. You can not find
a different row in the TID slot, because VACUUM will not have removed
a row that is possibly still visible to your transaction. (VACUUM
has no idea whether you're running under SERIALIZABLE rules or not,
and so it takes the conservative approach that any row you could ever
possibly have seen as good is still interesting.) But this guarantee
only lasts as long as your current transaction.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > I believe that's not necessarily true. If you
> select a tuple and it's
> > ctid and it's updated more than once with a vacuum
> in-between I believe
> > it could end up back in the same position, which
> would mean the same
> > ctid.
>
> This is the reason for the recommendation that you
> don't trust a TID for
> longer than one transaction. If you select a row
> and see it has TID
> such and such, and then later try to
> fetch/update/delete that row by
> TID, the worst that can happen is that you'll not
> find the row because
> some other xact has already updated or deleted it.
> You can not find
> a different row in the TID slot, because VACUUM will
> not have removed
> a row that is possibly still visible to your
> transaction. (VACUUM
> has no idea whether you're running under
> SERIALIZABLE rules or not,
> and so it takes the conservative approach that any
> row you could ever
> possibly have seen as good is still interesting.)
> But this guarantee
> only lasts as long as your current transaction.
>
> regards, tom lane
>

Just in case anyone following this thread gets a
little confused, my response was somewhat tangential
to the main discussion; I was talking of fetching the
record by primary key or such, and then comparing the
ctid values. Agreed that any other valid use of ctid
is dubious.

__________________________________
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

On Tue, Aug 23, 2005 at 06:42:33PM -0700, Jeff Eckermann wrote:
> > I believe that's not necessarily true. If you select
> > a tuple and it's
> > ctid and it's updated more than once with a vacuum
> > in-between I believe
> > it could end up back in the same position, which
> > would mean the same
> > ctid.
>
> True. But the probability of that happening would
> generally be low enough not to bother the designers of
> most applications.

Designers that don't care about their data, maybe. Here's the use case
that was implied:

Application selects a bunch of data to present to the user to be edited
User edits data even though it's not locked in the database
Application gets data and checks to see if it's changed. If it not,
*BLAM*, new changes are put into the database

Now, if that check for changed data fails with a false negative, you
just nuked data.

A better solution is to use a combination of a timestamp and a sequence.
Why both? Because it's possible for the clock to be set back (though
this is something best avoided), and a sequence will eventually roll
over. It's impossible to have a collision in this scenario unless you
roll the clock way back AND reset the sequence (assuming you're using an
integer sequence).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461

On Tue, Aug 23, 2005 at 11:36:31PM -0500, Jim C. Nasby wrote:

> A better solution is to use a combination of a timestamp and a sequence.
> Why both? Because it's possible for the clock to be set back (though
> this is something best avoided), and a sequence will eventually roll
> over. It's impossible to have a collision in this scenario unless you
> roll the clock way back AND reset the sequence (assuming you're using an
> integer sequence).
Or else use XMIN.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Jim C. Nasby wrote:
>
> A better solution is to use a combination of a timestamp and a sequence.
> Why both? Because it's possible for the clock to be set back (though
> this is something best avoided), and a sequence will eventually roll
> over.

With the default MAXVALUE of a postgresql sequence (9 quintillion or so)
you'd need a pretty amazingly fast cluster to roll one over, wouldn't you?
Of course if you choose to truncate them to something smaller they might,
but I'd see little benefit of both truncating and adding a timestamp.

On Aug 23, 2005, at 10:02 AM, Ilja Golshtein wrote:

> The only thing I am curios is ctid good for
> anything from user point of view?
>

I have a very specific use for it -- to bypass the index on an
update. Something like this:

select ctid,user_id from users where ...
... do stuff based on user_id ...
update users set last_mod=CURRENT_TIME where ctid='$ctid' and user_id=
$user_id

since I have already locked those rows earlier in the transaction I
worry not about anyone else updating those rows. However, the extra
safetynet of checking that the current row at $ctid is still the one
I want, I check that. If the row is not updated (ie, count 0
returned) then I do a standard update based just on the user_id which
is the PK.

When you add this up over millions of rows, it makes a difference to
bypass the PK index lookup every time.

Vivek Khera, Ph.D.
+1-301-869-4449 x806