Thread: Get the offset of a tuple inside a table

Hi,<br />When I using an index scan, can I get the offset of the tuple in the table?<br /><br />Thanks<br />--<br
/>Pei<br/> 

Pei He <hepeimail@gmail.com> wrote:
> When I using an index scan, can I get the offset of the tuple in
> the table?
Well, regardless of the type of scan, you can select the ctid
column, which gives the block number and the tuple number within the
block.
-Kevin

Hi Szymon, Kevin,
The offset is the order of a tuple in a Sequential Scan.

What I want to do is, for a given key return the tuples that Index scan can find, and return the rest tuples by a seq scan. So, I need to know which tuples have been returned by maintain a bitmap, and to avoid return the same tuple twice.

If I can know the offset of a tuple in the order of file scan, then I can force the seq scan to skip it.

By ctid, can I know how much tuples are in a block, is it an constant?

I think a more general solution would be build a hash table on ctid.
What do you think?

Thanks
--
Pei

Pei He  wrote:
> The offset is the order of a tuple in a Sequential Scan.
That's not a safe assumption.  Try starting a sequential scan against
a large table on one connection; then before it finishes, start the
same query on another connection.  The second query joins the one
already in progress and then starts over, "wrapping around".
-Kevin

I doesn't realized postgresql can be such clever.<br /><br />Another problem I just found is after one tuple is
deleted,there will be some slots unoccupied. The offset cannot be computed even know how much tuples a page can
contain.<br/><br />I need one hash table.<br /><br />Thanks<br />--<br />Pei<br /><br /><div class="gmail_quote">On
Tue,Sep 21, 2010 at 8:47 PM, Kevin Grittner <span dir="ltr"><<a
href="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left:
1ex;"><divclass="im">Pei He  wrote:<br /><br /> > The offset is the order of a tuple in a Sequential Scan.<br /><br
/></div>That'snot a safe assumption.  Try starting a sequential scan against<br /> a large table on one connection;
thenbefore it finishes, start the<br /> same query on another connection.  The second query joins the one<br /> already
inprogress and then starts over, "wrapping around".<br /><font color="#888888"><br /> -Kevin<br
/></font></blockquote></div><br/> 

Pei He <hepeimail@gmail.com> writes:
> What I want to do is, for a given key return the tuples that Index scan can
> find, and return the rest tuples by a seq scan. So, I need to know which
> tuples have been returned by maintain a bitmap, and to avoid return the same
> tuple twice.

> If I can know the offset of a tuple in the order of file scan, then I can
> force the seq scan to skip it.

As pointed out, "offset" is an unworkable concept here.  That's why the
tidbitmap code doesn't work with offsets; it works with tids.  You don't
really need to reinvent this wheel.  Go read the bitmapscan code.

(One wonders though what you think you are going to save if you have to
do a seqscan anyway.  Where's the advantage over just doing a seqscan?)
        regards, tom lane

Hi Tom,<br />The bitmapset works for me.<br /><br />I want to implement the operator for the following query:<br /><br
/>Select* from a left join b on <a href="http://a.id">a.id</a> = <a href="http://b.id">b.id</a> order by <a
href="http://b.id">b.id</a>;<br/><br />In a left outer join, I want the tuples that have matches in the inner table
appearfirst. So, the order by clause is need.<br /><br />If there is a index on <a href="http://a.id">a.id</a>, I can
usethe tuples in b to probe the index.<br /><br />After return all the tuples retrieved through index, it needs to
returnthe rest tuples in a, because it is a left outer join in the query.<br /><br />What I need to do is remember what
havebeen returned by the index, and avoid to return it twice.<br /><br />The bitmapscan needs to remember what have to
beenretrieved later, so it used the tidbitmap. But, for me, I need the bitmapset.<br /> <br />Thanks, your reply helps
meto find the bitmapset.<br /><br />Regards<br />--<br />Pei<br /><br /><br /><div class="gmail_quote">On Tue, Sep 21,
2010at 9:32 PM, Tom Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"><div class="im">Pei He <<a href="mailto:hepeimail@gmail.com">hepeimail@gmail.com</a>>
writes:<br/> > What I want to do is, for a given key return the tuples that Index scan can<br /> > find, and
returnthe rest tuples by a seq scan. So, I need to know which<br /> > tuples have been returned by maintain a
bitmap,and to avoid return the same<br /> > tuple twice.<br /><br /> > If I can know the offset of a tuple in the
orderof file scan, then I can<br /> > force the seq scan to skip it.<br /><br /></div>As pointed out, "offset" is an
unworkableconcept here.  That's why the<br /> tidbitmap code doesn't work with offsets; it works with tids.  You
don't<br/> really need to reinvent this wheel.  Go read the bitmapscan code.<br /><br /> (One wonders though what you
thinkyou are going to save if you have to<br /> do a seqscan anyway.  Where's the advantage over just doing a
seqscan?)<br/><br />                        regards, tom lane<br /></blockquote></div><br /> 

On Tue, Sep 21, 2010 at 10:58 PM, Pei He <hepeimail@gmail.com> wrote:
> Hi Tom,
> The bitmapset works for me.
>
> I want to implement the operator for the following query:
>
> Select * from a left join b on a.id = b.id order by b.id;
>
> In a left outer join, I want the tuples that have matches in the inner table
> appear first. So, the order by clause is need.

Why can't you just write SELECT * FROM a LEFT JOIN b ON a.id = b.id
ORDER BY b.id NULLS FIRST?  "I want my query results in a different
order" is almost never something that requires modifying the source
code.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company