PostgreSQL: Experiments to find fastest mix of type+index to using as flag

Do you know a relation between field type and data selecting? No? Common, go inside..

In this experiment used default configuration of PgSQL. Make a table, at the first: CREATE TABLE test ( id serial, flag bit(1) NOT NULL, added TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); and fill it with random data (10 million records, for example): INSERT INTO test (flag) SELECT flag::bit FROM ( SELECT generate_series(0,10000000) as id, CASE WHEN random() < 0.25 THEN 1 ELSE 0 END as flag ) e;

Lets check an execution time without indexing: After alter - vacuum-ize a table.
TypeBITBOOLEANINTEGER
ms1200..1208669..675827..841

Now do same things with index and look at performance what we made.
TypeBITBOOLEANINTEGER
ms361..374321..352317..320

As you can see, using an indexes gives a same relative results, but indexing with integer type small faster.
The reason of it is lack of data casting of types boolean and bit, but integer type is simple and make no type casting
One other reason - a bit type is 6 bytes vs 4 of integer.
And finally, if your want better perfomance - K.I.S.S. ("Keep it simple, stipid!")

Leave a Reply

Your email address will not be published. Required fields are marked *