Обсуждение: What's faster: value of 0 or NULL with index
Hi, I'm thinking about, what might be faster on SELECTs: a column with index which is NOT NULL and takes the value of 0 or a column which can take the NULL value instead of 0, also with index. My feeling sais, that 0 and NOT NULL should be a lot more faster, but perhaps it's not true? bye Alvar -- Alvar C.H. Freude | alvar.freude@merz-akademie.de Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
On Mon, Dec 11, 2000 at 04:28:24AM +0100, some SMTP stream spewed forth: > Hi, > > I'm thinking about, what might be faster on SELECTs: a column with index > which is NOT NULL and takes the value of 0 or a column which can take > the NULL value instead of 0, also with index. > > My feeling sais, that 0 and NOT NULL should be a lot more faster, but > perhaps it's not true? If your SELECT uses the index on the NOT NULL column, then yes, the indexed 0 should be faster. I think it takes less space as well.(?) gh > > > bye > Alvar > > > -- > Alvar C.H. Freude | alvar.freude@merz-akademie.de > > Demo: http://www.online-demonstration.org/ | Mach mit! > Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit > Blast-EN: http://www.a-blast.org/ | Blast/english
GH <grasshacker@over-yonder.net> writes: > On Mon, Dec 11, 2000 at 04:28:24AM +0100, some SMTP stream spewed forth: >> My feeling sais, that 0 and NOT NULL should be a lot more faster, but >> perhaps it's not true? > If your SELECT uses the index on the NOT NULL column, then yes, the > indexed 0 should be faster. I think it takes less space as well.(?) No, a NULL index entry should be a little smaller than a non-null one. I doubt you could measure any speed difference, but if there were any it'd probably be in favor of the solution with NULLs. Also, if you expect to have a lot of these dummy entries, then it's a good idea to represent them as NULL rather than a real value, because the NULLs won't skew the planner's statistics about the column's most common value. BUT: currently, a query like "WHERE foo = 0" can use an index, whereas the planner does not consider an index for query like "WHERE foo IS NULL". So if you intend to actually search for the dummy entries, and you need that to be fast, you'd have to use 0. regards, tom lane