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