Search Postgresql Archives

Re: Index size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks everyone for your feedback so far. I've done a bit more digging:

MySQL in MBytes (about 350 million rows):

index_user_event_on_what_category_id_created_at_latlng | 22806.00
index_user_event_for_reporting | 18211.00
index_user_event_on_created_at | 9519.00
index_user_event_on_user_id | 6884.00
index_user_event_on_poi_id | 4891.00
index_user_event_on_deal_id | 3979.00

Postgres (about 250 million rows):

index_user_event_on_what_category_id_created_at_latlng | 25 GB
index_user_event_for_reporting | 19 GB
index_user_event_on_created_at | 7445 MB
index_user_event_on_user_id | 7274 MB
index_user_event_on_deal_id | 7132 MB
index_user_event_on_poi_id | 7099 MB

So, the index is a bit bigger, plus there is also the PKEY index which
increases disk usage by another whole index. Keep in mind in the
above, MySQL has about 40% more data.

With some indexes, it looks like MySQL might not be adding all data to
the index (e.g. ignoring NULL values). Does MySQL ignore null values
in an index? Can we get the same behaviour in Postgres to minimise
usage? What would be the recommendation here?

For the composite indexes, if any value is null, we are also less
interested in it, so it could be ignored from the index.

Thanks,
Samuel


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux