Thread: Scan Keys
I'm a bit confused about how scan keys work. Is there any simple way given a list of Datums of the same type as the index tuple attributes to get all matching index entries? This is for a non-system index. It seems like the only place in the code where non-system index lookups are done is nodeIndexscan.c where it has the strategy number, subtype, and function to use from the work that's previously been done on the expression. But I want to do something more like what btree does inside btinsert where it knows that no cross-type functions could be necessary and the only function of interest is equality. I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like btree does but _bt_preprocess_keys runs into problems without a valid strategy number. And in any case that would be btree specific which seems like it ought not be necessary. -- greg
On Wed, Jul 05, 2006 at 12:00:05PM -0400, Greg Stark wrote: > > I'm a bit confused about how scan keys work. Is there any simple way given a > list of Datums of the same type as the index tuple attributes to get all > matching index entries? This is for a non-system index. A scankey determines which values you want. It consists of a value and an operator. Using that the index code returns tuples matching. So if you want all values equal to 4, you pass '4' for the Datum and the Equal strategy, with the operator as '='. The info you need is in the operator class. In a sense you do need to know the type of index you're scanning, not all indexes use the same strategy numbers. > But I want to do something more like what btree does inside btinsert where it > knows that no cross-type functions could be necessary and the only function of > interest is equality. By the time the btree code gets involved, everything in the scankey should already have been filled in. I don't beleive the code actually checks if the operator is of the type you specify. Hope this helps a bit, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes: > The info you need is in the operator class. In a sense you do need to > know the type of index you're scanning, not all indexes use the same > strategy numbers. Well what was tripping me up was figuring out the operator class. I just realized it's in the index's Relation object. But yes what you describe is really a problem. Even given the operator class there's no way for me to know which strategy number to pick. There might not be any strategy number for equals in which case I'm in trouble. -- greg
On Wed, Jul 05, 2006 at 09:14:21PM -0400, Greg Stark wrote: > Well what was tripping me up was figuring out the operator class. I just > realized it's in the index's Relation object. > > But yes what you describe is really a problem. Even given the operator class > there's no way for me to know which strategy number to pick. There might not > be any strategy number for equals in which case I'm in trouble. Well yes, that's an issue. Currently it's assumed by various parts of the backend that the EqualStrategy operator of a btree operator class is what you can use to decide what's equal. Although it's not really necessary, there is currently nothing else in the system that really tells you the answer to the question you ask... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Greg Stark <gsstark@mit.edu> writes:
> I'm a bit confused about how scan keys work. Is there any simple way given a
> list of Datums of the same type as the index tuple attributes to get all
> matching index entries? This is for a non-system index.
Define "matching".
> I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like
> btree does but _bt_preprocess_keys runs into problems without a valid strategy
> number. And in any case that would be btree specific which seems like it ought
> not be necessary.
There's no particularly good reason to suppose that a non-btree index
necessarily supports equality probes at all. For instance if you're
using GIST or GIN for full-text searching, the index might only know
about component words, not the whole strings that are in the table.
Opclasses designed for spatial databases might conceivably not have
equality either (though that's a bit more of a stretch).
As Martijn pointed out, we rely on btree-opclass equality as the main
means of deciding what equality "really is". I don't think it'd be
wise to insist that every index opclass, no matter what its purpose,
has to include an equality operator.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > > I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like > > btree does but _bt_preprocess_keys runs into problems without a valid strategy > > number. And in any case that would be btree specific which seems like it ought > > not be necessary. > > There's no particularly good reason to suppose that a non-btree index > necessarily supports equality probes at all. For instance if you're > using GIST or GIN for full-text searching, the index might only know > about component words, not the whole strings that are in the table. > Opclasses designed for spatial databases might conceivably not have > equality either (though that's a bit more of a stretch). AFAIK even GIST indexes can fetch me a reasonably limited list of tuples that might be equal. It might include significantly more tuples than just what I'm looking for but it's much better than doing a full index scan. But on that note, is it ok to use the bulkdelete index AM methods for non-vacuum purposes as long as there's only one such process running at a time? Presumably that means taking an ShareUpdateExclusiveLock on the indexrelation or a ExclusiveLock on the pg_index line? > As Martijn pointed out, we rely on btree-opclass equality as the main > means of deciding what equality "really is". I don't think it'd be > wise to insist that every index opclass, no matter what its purpose, > has to include an equality operator. I'm having trouble picturing any useful index where there's no operator close to equality. But I'll concede that that may be a failure of my imagination :) -- greg
Greg Stark <gsstark@mit.edu> writes:
> But on that note, is it ok to use the bulkdelete index AM methods for
> non-vacuum purposes
Um, what would those be?
ambulkdelete and amvacuumcleanup are most certainly not designed to be
used in any context other than VACUUM. You might be able to abuse them
for some other purpose, but don't expect a warranty.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > But on that note, is it ok to use the bulkdelete index AM methods for > > non-vacuum purposes > > Um, what would those be? I'm sure with a little work I can imagine lots of reasons to want to pull out all the index tuples from an index other than vacuum. I can't actually name any right now, but I'm sure I'll think of some :) > ambulkdelete and amvacuumcleanup are most certainly not designed to be > used in any context other than VACUUM. You might be able to abuse them > for some other purpose, but don't expect a warranty. What I'm doing now is calling ambulkdelete with a callback that returns false for every tuple and stuffs the item pointer into a data structure. It seems to be working but I haven't tested it thoroughly yet. I'm worried that ambulkdelete might have side effects on the index that aren't obvious. Other than bumping the vacuum cycle id I don't see any in a quick scan of the btree code. But I could have missed something or other access methods could behave differently. I would be happier with a generic "index sequential scan" interface. I realize the cycle id trick restricts any such api to a single scanner at a time which is fine for my purposes. Actually is that really true? Wouldn't the cycle id setup work fine if a subsequent scan started and the cycle id was bumped a second time? If you find a page that was split with a cycle id greater than yours then you still know it was split after you started. As long as the cycle id doesn't wrap while a scan is proceeding it should be fine. Perhaps tagging the split pages with the xid of the most recent scan instead of a separate cycle id concept? -- greg