Re: Index size

On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams <
space(dot)ship(dot)traveller(at)gmail(dot)com> wrote:

> Thanks Kevin, that makes sense. Yeah, I understand the architectural
> difference a bit more now. I also read that when you change a column
> which is not index, all the indexes for that row need to be updated
> anyway. Is that correct?
>
> On 7 December 2016 at 05:27, <kbrannen(at)pwhome(dot)com> wrote:
> > Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)com> wrote:
> >> So, uh, my main question was, does MySQL add null values to an index,
> and is this different from Postgres...
> >
> > Samuel,
> >
> > A quick google says that Mysql does index NULLs. Ask a Mysql group to
> get a more definitive answer.
> >
> > More relevant to your original question, I'll go out on a limb as I
> struggle to recall a fuzzy memory.
> >
> > The difference between Mysql and Postgresql is fundamental architecture,
> so yes the index creation will be very different, as others have said. IIRC
> (and I may not be), Mysql stores where a row is on the disk via the PK
> index. That means that secondary indexes point to the proper row in the PK
> index, which does mean that when you use a secondary index to get data that
> there is a double look up. They claim that's faster for updates and other
> stuff because a change to a row only requires 1 index to be changed.
> >
> > Postgresql stores the direct disk location in each index, which slows
> down updates a little, but makes selects faster (and I find this really
> amusing because so many people say Mysql is great because it's fast at
> reads, yet architecturally PG is faster). If I'm wrong here, I'm sure I'll
> be corrected. :)
> >
> > So you can see that Mysql indexes should be smaller than PG indexes
> because of what they carry. Personally, I think the diff is small enough
> I'm not going to worry about it, but math is such that some numbers are
> smaller than others. :) So that should explain what you're seeing.
> >
> > My opinion is that you shouldn't worry about the index size. Which DB
> does what you want the best? That obviously depends on what your needs are,
> but after using both Mysql and PG, I'll take PG whenever possible, thank
> you. Mysql has gotten better over the last 5-8 years, but there are still
> many pits of quicksand ready to swallow you up there that PG doesn't have.
> If you know where those pits are and/or don't travel into that part of the
> jungle, great for you; personally, I prefer to get the job done without
> having to watch where I'm walking. ;)
> >
> > HTH,
> > Kevin
> >
> >
> > --
> > 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
>
>
> --
> 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
>

*>I also read that when you change a column which is not index, all the
>indexes for that row need to be updated anyway. Is that correct?That is
not correct. Indexes are changed under the following conditions:A. An
insert is done to the table which involves an index.B. A delete is done to
the table which involves an index.C. An update is done that involves
columns included in an index.*

*D. An index is REINDEXed *

*Indexes point to the tid of the row for which the column(s) in the
indexare involved. So if columns updated are not involved in the index,
there is no need to change the
index.https://www.postgresql.org/docs/9.4/static/sql-createindex.html
<https://www.postgresql.org/docs/9.4/static/sql-createindex.html>https://www.postgresql.org/docs/9.4/static/sql-reindex.html
<https://www.postgresql.org/docs/9.4/static/sql-reindex.html>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.