Thread: index-only scan is missing the INCLUDE feature
Hi all,
as far as i looked around about the new feature: index-only scan, i guess this feature will not include the option such as ms-sql INCLUDE.
well, i have a table with columns: a,b,c
i query the table like this: select a,c from table where a=x and b=y
as for now, i have unique-index on (a,b)
in the future (when upgrading to 9.2), i would like to have unique-index on (a,b, INCLUDE c). but that wont be possible (right?).
so... in order to have index-only scan, i will have to create an index like (a,b,c), but this has problems:
1. i lose the uniqueness enforcement of (a,b), unless, i will create 2 indexes: (a,b) and (a,b,c).
2. every update to column c would result in an unnecessary index-key-update (or what ever you call that operation), which is not just updating a tuple, but also an attempt to re-ordering it(!).
3. i just wonder: practically there is uniqueness of (a,b). now, if i create index like (a,b,c) the optimizer dose not know about the uniqueness of (a,b), therefore i afraid, it may not pick the best query-plan..
Thanks for any comment.
On 06/20/2012 12:46 PM, Eyal Wilde wrote:
For those of us who don't know MS-SQL, can you give a quick explanation of what the INCLUDE keyword in an index definition is expected to do, or some documentation references? It's possible to guess it somewhat from your description, but it's helpful to be specific when asking a question about features from another DBMS.Hi all,as far as i looked around about the new feature: index-only scan, i guess this feature will not include the option such as ms-sql INCLUDE.
--
Craig Ringer
On 06/20/2012 09:11 AM, Craig Ringer wrote: > For those of us who don't know MS-SQL, can you give a quick > explanation of what the INCLUDE keyword in an index definition is > expected to do, or some documentation references? He's talking about what MS SQL Server commonly calls a "covering index." In these cases, you can specify columns to be included in the index, but not actually part of the calculated hash. This prevents a trip to the table data, so selects can be serviced entirely by an index scan. PostgreSQL is about half way there by allowing index-only scans, though I've no idea if they intend on adding further functionality like this. Effectively you can trade index bloat for query speed. But considering the differences between the engines, it might not be necessary. I couldn't say. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On 06/20/2012 11:32 PM, Shaun Thomas wrote: > On 06/20/2012 09:11 AM, Craig Ringer wrote: > >> For those of us who don't know MS-SQL, can you give a quick >> explanation of what the INCLUDE keyword in an index definition is >> expected to do, or some documentation references? > > He's talking about what MS SQL Server commonly calls a "covering > index." In these cases, you can specify columns to be included in the > index, but not actually part of the calculated hash. This prevents a > trip to the table data, so selects can be serviced entirely by an > index scan. Oh, OK, so it's a covering index with added fields that don't form part of the searchable index structure to make the index a little less expensive than a fully covering index on all the columns of interest. Fair enough. Thanks for the explanation. Eyal, you'll get a better response to questions about other DBMSs if you explain what you need/want to do with the desired feature and what that feature does in the other DBMS. > > PostgreSQL is about half way there by allowing index-only scans, > though I've no idea if they intend on adding further functionality > like this. There's certainly lots of interest in adding more, but not that many people with the expertise to be able to do it - and fewer still who're paid to work on Pg so they have time to focus on it. Covering indexes with Pg's MVCC model seem to be particularly challenging, too. -- Craig Ringer
Le jeudi 21 juin 2012 04:45:41, Craig Ringer a écrit : > On 06/20/2012 11:32 PM, Shaun Thomas wrote: > > On 06/20/2012 09:11 AM, Craig Ringer wrote: > >> For those of us who don't know MS-SQL, can you give a quick > >> explanation of what the INCLUDE keyword in an index definition is > >> expected to do, or some documentation references? > > > > He's talking about what MS SQL Server commonly calls a "covering > > index." In these cases, you can specify columns to be included in the > > index, but not actually part of the calculated hash. This prevents a > > trip to the table data, so selects can be serviced entirely by an > > index scan. > > Oh, OK, so it's a covering index with added fields that don't form part > of the searchable index structure to make the index a little less > expensive than a fully covering index on all the columns of interest. > Fair enough. Thanks for the explanation. > > Eyal, you'll get a better response to questions about other DBMSs if you > explain what you need/want to do with the desired feature and what that > feature does in the other DBMS. > > > PostgreSQL is about half way there by allowing index-only scans, > > though I've no idea if they intend on adding further functionality > > like this. > > There's certainly lots of interest in adding more, but not that many > people with the expertise to be able to do it - and fewer still who're > paid to work on Pg so they have time to focus on it. Covering indexes > with Pg's MVCC model seem to be particularly challenging, too. There was a recent thread on -hackers about index with UNIQUEness of some columns only. The objective was near the one you describe here. So you're not alone looking after that. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation