Re: Index size

On Sun, Dec 4, 2016 at 4:43 AM, Samuel Williams <
space(dot)ship(dot)traveller(at)gmail(dot)com> wrote:

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

*Samuel, *

*Please note that the accepted convention in this forum is to bottom post.
Please do not top post.>Melvin, of course there are differences. However, I
suspect there are at least SOME tangible differences which can
be identified.*

*I have to ask, WHY do you think it is important to identify any
differences in the index methods.*

*What is important is that you understand why you need indexes and how they
are used.Tutorial -
Indexeshttps://www.tutorialspoint.com/postgresql/postgresql_indexes.htm
<https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm>CREATE
INDEXhttps://www.postgresql.org/docs/9.4/static/sql-createindex.html
<https://www.postgresql.org/docs/9.4/static/sql-createindex.html>*
--

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.