December 30, 2016

vacuum analyze new big table

I have a big table(compared to others table), yesterday I rebuilt it with some new columns, because I added an id as primary key, so I want this column on the first place of table, so I created a new table with new columns and old columns, populated data from old table. After rebuilding this big table, some queries are slower than before, if I add some odd or unused ordering condition, that queries are fast as before. Read more

October 16, 2016

Postgresql 9.5 GiST index on int array

Last week I build an GiST index for a table with 200+ rows (two columns: id INT, sp_ids INT[]) , it spent me almost 10 mins. There are only two elements in every int array, but those value are larger than normal integers, they are 7~8 bits integers. After chat via postgresql irc, someone pointed that I should use gist__intbig_ops, not default(gist__int_ops). gist__int_ops is optimized for small ints, and the other is suit for all small and big ints. Read more

Powered by Hugo & Kiss.