DB select query performance when queried by partial key of primary key My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, if I add an index for {a}?
Assuming your DBMS is using BTree indexes, there is a chance that there may be a small speed-up in certain situations.

To perform a lookup on a BTree the system starts at the root node, follows links through the intermediate nodes to the leaf node.  The fewer columns there are in the index the more index keys will fit in a page and the fewer levels will be needed in the BTree. With fewer levels there will be fewer page reads for each lookup.

There are a lot of caveats to this. For example, in most realistic scenarios the fanout in a BTree is such that for medium-large table the index becomes 3 levels deep and stays that way for a very long time, irrespective of the keys in the index. Also it is likely most of your index pages are in memory already so the additional read time is trivial.

Against the slim possibility of increased read speed you must balance the absolute certainty of decreased write speed. Each row inserted must now be written to the the table, the {a} index and the {a,b} index.

Most optimisers will consider indexes where the leading keys are matched in the query. Say you define and index on {a,b,c,d}. That would be considered for queries that have predicates on a, a and b, a and b and c, or all four columns. (I say "consider" and not "used". Choosing which index to actually use is a whole different discussion.) So for a wide class of queries you get some potential benefit without the addition write overhead. Here's more detail on that.

How much storage will an index take?  Order-of-magnitude I'd say the size of the columns times the number of rows.  So if you have an integer (4bytes) and 1 million rows, the index will take about 4MB.  It might be 3MB, it might be 10MB, depending on a lot of things.  But it won't be 1KB and it won't be 1GB.

As a rule of thumb I would avoid keys with duplicate leading indexes.  If you identify a very specific use case, after careful testing, and having considered the load on the system as a whole, then you may think of adding some. Not as a matter of course, however.