Re: Index size - Mailing list pgsql-general

On Sun, Dec 4, 2016 at 4:43 AM, Samuel Williams <space.ship.traveller@gmail.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@gmail.com> wrote:

 

 

On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins <steve@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.

 

Samuel,

Please note that the accepted convention in this forum is to bottom post. Please do not top post.