Thread: quick question: index optimisations on small tables
If I have: CREATE TABLE small ( key integer PRIMARY KEY, value text ); and assuming there are only enough rows to fit in one page, doesn't it make sense to use the index instead of a seq. scan for queries of type SELECT value FROM small WHERE key = 12345; Since it can get the answer straight out of the index, and if there are potentially numerous rows, looking up a b-tree is faster than a linear search? - Andrew
Andrew Snow writes: > Since it can get the answer straight out of the index, and if there are > potentially numerous rows, looking up a b-tree is faster than a linear > search? You can't get the "answer straight out of the index". You always need to check back in the real table to see whether the row is visible to your transaction. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Andrew Snow wrote: > > If I have: > > CREATE TABLE small ( > key integer PRIMARY KEY, > value text > ); > > and assuming there are only enough rows to fit in one page, doesn't it > make sense to use the index instead of a seq. scan for queries of type > > SELECT value FROM small WHERE key = 12345; > Since you have declared the column 'key' as PRIMARY KEY there is an index on column 'key' anyway and SELECT value FROM small where key = 12345 will use that index: on my system psql said: omicron=# EXPLAIN SELECT value FROM small WHERE key = 12345; NOTICE: QUERY PLAN: Index Scan using small_pkey on small (cost=0.00..8.14 rows=10 width=12) > Since it can get the answer straight out of the index, and if there are > potentially numerous rows, looking up a b-tree is faster than a linear > search? Looking up from an index is of course faster than a seq. scan (in almost all cases). Arne. > TIP 4: Don't 'kill -9' the postmaster
> > Since you have declared the column 'key' as PRIMARY KEY there > is an index on column 'key' anyway and SELECT value FROM > small where key = 12345 will use that index: on my system psql said: > > omicron=# EXPLAIN SELECT value FROM small WHERE key = 12345; > NOTICE: QUERY PLAN: > > Index Scan using small_pkey on small (cost=0.00..8.14 > rows=10 width=12) > > > Since it can get the answer straight out of the index, and if there > > are potentially numerous rows, looking up a b-tree is faster than a > > linear search? > > Looking up from an index is of course faster than a seq. scan > (in almost all cases). Hrmm... I have 26 rows in mine at the moment, and after vacuum analyzing, it uses a seq. scan. How come yours used the index? I thought mine wasn't using an index because postgres won't use an index until the table is "big enough". But if an index page is already in cache.. surely it'd be faster using it than doing a seq. scan. (Yes, I know its a small table, but I think the worst case for seq. scan would be a fair bit worse than for the index, and every little bit counts, right?) - Andrew
On Fri, 31 Aug 2001, Andrew Snow wrote: > Hrmm... I have 26 rows in mine at the moment, and after vacuum > analyzing, it uses a seq. scan. How come yours used the index? I > thought mine wasn't using an index because postgres won't use an index > until the table is "big enough". > > But if an index page is already in cache.. surely it'd be faster using > it than doing a seq. scan. > > (Yes, I know its a small table, but I think the worst case for seq. scan > would be a fair bit worse than for the index, and every little bit > counts, right?) If the table is small enough to fit in one page, a sequence scan across those rows may be faster than the index scan since the index scan will need to read two pages (one for the index, one for the heap -- the visibility info is only in the heap so that must be consulted for each index match)