Re: Index size

Melvin, of course there are differences. However, I suspect there are at
least SOME tangible differences which can be identified.

On 4 December 2016 at 15:53, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:

>
>
> 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.
>