Re: [GENERAL] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Tom Lane schrieb: Marinos Yannikos writes: "i_a" btree (a) "i_ab" btree (a, b) I suspect that these indexes are exactly the same size --- look at pg_class.relpages or use the pg_relation_size() function to verify. For some reason, the first one is actually about twice the size of the second (175458 relpages vs. 88186, pg_relation_size() confirms it). It wouldn't really matter anyway because the actual runtime should be pretty much the same too. The runtime is unfortunately worse in some cases due to the degradation we've been seeing (lots of INSERT/UPDATE on this table), but I think we fixed this with nightly REINDEX runs on the 2-dimensional indexes (which is probably also the reason for the odd sizes above). I guess we can just drop the first index then. Thanks, -mjy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Marinos Yannikos writes: > Recent versions of PostgreSQL seem to prefer 2d indexes somehow: > for a table "foo" with > "i_a" btree (a) > "i_ab" btree (a, b) > SELECT * FROM foo WHERE a=123 > will often use "i_ab" and not "i_a" (even right after ANALYZE). I suspect that these indexes are exactly the same size --- look at pg_class.relpages or use the pg_relation_size() function to verify. If they are, the computed access cost will be exactly the same and which one gets picked is an implementation artifact. (I think that in the current code the one that has the larger OID gets picked, but that's not something I'd suggest you rely on.) It wouldn't really matter anyway because the actual runtime should be pretty much the same too. The most likely reason for this to happen is that you're talking about two int4 columns and you're on a 64-bit machine that is going to align index entries to 8-byte boundaries. The one-column index isn't actually any smaller because of alignment padding :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Recent versions of PostgreSQL seem to prefer 2d indexes somehow: for a table "foo" with "i_a" btree (a) "i_ab" btree (a, b) SELECT * FROM foo WHERE a=123 will often use "i_ab" and not "i_a" (even right after ANALYZE). This raises some questions: - is there even any benefit in still having both these indexes? (can some operations still use "i_a" only or is "i_ab" always a sufficient replacement for "i_a"?) - is this even working as intended? in my experience (can't back it up with numbers atm.), 2-dimensional indexes are often slower and they degrade noticeably over time. Without knowing the implementation, I'd assume that using "i_ab" would usually require more page fetches than using "i_a" for the above query. Regards, Marinos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
