On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins <st...@blighty.com> wrote:
> > > On Dec 3, 2016, at 3:57 PM, Samuel Williams <space.ship.traveller@gmail. > com> wrote: > > > > 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? > > It's unlikely anyone will be able to usefully answer the questions you > should be asking without seeing the schema and index definitions, > and maybe some clues about how you're querying the data. > > Cheers, > Steve > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > You are comparing apples to oranges. MySQL and PostgreSQL engines are different by design, so likewise the size of the indexes will be different. You may as well ask why a 2015 Porsche 911 Carrera 6 cyl, 3.4 L, Auto(AM-S7) gets 22 MPG, but the 2016 Jaguar XF MPG gets 24 mpg. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
