Re: Index size

On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:

>
> > On Dec 3, 2016, at 3:57 PM, Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)
> 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(at)postgresql(dot)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.