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