Re: [GENERAL] Index size

> On Dec 11, 2016, at 08:37, Melvin Davidson wrote: > > Yes, I see your point, but the case where the row does not fit into the same > block > would only occur with unlimited field types such as var[], bytea[], etc. No, that's not correct either. A block can fill up for any number of reasons

Re: [GENERAL] Index size

On Sat, Dec 10, 2016 at 5:42 PM, Peter J. Holzer wrote: > On 2016-12-09 21:45:35 -0500, Melvin Davidson wrote: > > On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams < > space.ship.travel...@gmail.com> > > wrote: > > >I also read that when you change a column which is not index, all the > > >indexes

Re: [GENERAL] Index size

On 2016-12-09 21:45:35 -0500, Melvin Davidson wrote: > On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams > > wrote: > >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 chan

Re: [GENERAL] Index size

On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams < space.ship.travel...@gmail.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 updat

Re: [GENERAL] Index size

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, wrote: > Samuel Williams wrote: >>

Re: [GENERAL] Index size

Samuel Williams 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

Re: [GENERAL] Index size

On Mon, Dec 5, 2016 at 8:06 PM, Samuel Williams < space.ship.travel...@gmail.com> wrote: > Melvin, uh... I'm a software engineer... since when was it a problem to > want to know how things work and why they are different? If you have > nothing to contribute of a relevant technical nature, please d

Re: [GENERAL] Index size

On 12/03/2016 03:57 PM, Samuel Williams wrote: 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 he

Re: [GENERAL] Index size

Melvin, uh... I'm a software engineer... since when was it a problem to want to know how things work and why they are different? If you have nothing to contribute of a relevant technical nature, please don't reply, I'm really not interested.

Re: [GENERAL] Index size

På søndag 04. desember 2016 kl. 16:51:54, skrev Melvin Davidson < melvin6...@gmail.com >: On Sun, Dec 4, 2016 at 4:43 AM, Samuel Williams < space.ship.travel...@gmail.com > wrote: Melvin, of course there are differences. However

Re: [GENERAL] Index size

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 wrote: > >> >> >> On Sat,

Re: [GENERAL] Index size

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 wrote: > > > On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins wrote: > >> >> > On Dec 3, 2016, at 3:57 PM, Samuel Williams <

Re: [GENERAL] Index size

So, uh, my main question was, does MySQL add null values to an index, and is this different from Postgres. The schema is irrelevant, except that the column allows null values. I noticed when you create an index you can add a where clause. Could it be I should add WHERE the fields are not null? Her

Re: [GENERAL] Index size

On Sat, Dec 3, 2016 at 9:32 PM, Steve Atkins wrote: > > > On Dec 3, 2016, at 3:57 PM, Samuel Williams 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

Re: [GENERAL] Index size

> On Dec 3, 2016, at 3:57 PM, Samuel Williams > 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 >

Re: [GENERAL] Index size

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

Re: [GENERAL] Index size

On 11/30/2016 03:38 AM, Samuel Williams wrote: Is there any reason why for the same data set, and same indexes, that the data in postgres would be significantly larger than innodb/mariadb? Hard to say without the table/index definitions and some indication of what the data is. What version

Re: [GENERAL] Index size

On Thu, Dec 01, 2016 at 12:38:37AM +1300, Samuel Williams wrote: > Is there any reason why for the same data set, and same indexes, that > the data in postgres would be significantly larger than > innodb/mariadb? Sure: because they do entirely different things on-disk. Regards, Karsten -- GPG k

Re: [GENERAL] Index size

Is there any reason why for the same data set, and same indexes, that the data in postgres would be significantly larger than innodb/mariadb? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gener

Re: [GENERAL] Index size

On 11/29/2016 03:30 PM, Samuel Williams wrote: I'd like to understand a bit more about indexes in PG. https://www.postgresql.org/docs/9.5/static/indexam.html "An index is effectively a mapping from some data key values to tuple identifiers, or TIDs, of row versions (tuples) in the index's par

Re: [GENERAL] Index Size

On 30/05/11 20:53, Nick Raj wrote: > Hi, > > Cube code provided by postgres contrib folder. It uses the NDBOX structure. > On creating index, it's size increase at a high rate. Here's what I get on 8.4 with a cleaned up test case. It uses the original poster's data ( output_vehicle.sql ) and just

Re: [GENERAL] Index Size

On Tue, May 31, 2011 at 8:50 AM, Tom Lane wrote: > Craig Ringer writes: > > On 05/30/2011 08:53 PM, Nick Raj wrote: > >> Cube code provided by postgres contrib folder. It uses the NDBOX > structure. > >> On creating index, it's size increase at a high rate. > > > [snip] > > >> Can some one tell

Re: [GENERAL] Index Size

Craig Ringer writes: > On 05/30/2011 08:53 PM, Nick Raj wrote: >> Cube code provided by postgres contrib folder. It uses the NDBOX structure. >> On creating index, it's size increase at a high rate. > [snip] >> Can some one tell me why index is becoming so large? >> How to compress or reduce its

Re: [GENERAL] Index Size

On 05/30/2011 08:53 PM, Nick Raj wrote: Hi, Cube code provided by postgres contrib folder. It uses the NDBOX structure. On creating index, it's size increase at a high rate. [snip] Can some one tell me why index is becoming so large? How to compress or reduce its size? It'd help if you inc

Re: [GENERAL] Index size

All you said are wright. But it 's not so difficult for postgresql to hold on a bit attribute attached to each table the information, whether there is done an insertion/deletion/update to a clustered table or not. And i guess, postgresql would already implement this simply alternative. > Easy,

Re: [GENERAL] Index size

On Wed, Mar 02, 2005 at 11:30:58PM +0200, Ioannis Theoharis wrote: > On Wed, 2 Mar 2005, Martijn van Oosterhout wrote: > > What makes you think that? Clustering is nice, but postgresql needs to > > get the right answer and that the table in clustered is not something > > postgresql can rely on. >

Re: [GENERAL] Index size

On Wed, Mar 02, 2005 at 10:08:58PM +0200, Ioannis Theoharis wrote: > I have a relation like this: (att0 varchar(1000), att1 int4) > > i create a b-tree index on att1 () > i cluster my raltion according to index > > now i have a query > select* > form tc20 > where att1<=90

Re: [GENERAL] Index size

On Wed, 2 Mar 2005, Tatsuo Ishii wrote: > > An other question: > > > > Is there any way to prevent duplicates on btree index attribute, > > PERMITTING them on table? > > I can't think of any usefull usage for such an index. Can you explain > why you need it? I have a relation like this: (att0 v

Re: [GENERAL] Index size

Ioannis Theoharis <[EMAIL PROTECTED]> writes: > Where can i find a documentation with technical analysis for all (if > possible) of components of postgres? Read the source code. regards, tom lane ---(end of broadcast)--- TIP

Re: [GENERAL] Index size

On Tue, 1 Mar 2005, Tom Lane wrote: > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > So it seems Ioannis' number was not taken immediately after a CREATE > > INDEX operation? > > I would guess not, but it's up to him to say. If it is a number derived > after some period of normal operation, then

Re: [GENERAL] Index size

Tatsuo Ishii <[EMAIL PROTECTED]> writes: > So it seems Ioannis' number was not taken immediately after a CREATE > INDEX operation? I would guess not, but it's up to him to say. If it is a number derived after some period of normal operation, then his result agrees with the theory that says 70% is

Re: [GENERAL] Index size

> > Interesting. Right after CREATE INDEX for a int4 column using pgbench > > -s 10(1,000,000 tuples), I got 2184 leaf pages. From my caliculation > > the number of leaf pages is expected to 1965, which is 100% full case > > assumption of course. So 1965/2184 = 0.8997 = 90% is actually used? > > S

Re: [GENERAL] Index size

Tatsuo Ishii <[EMAIL PROTECTED]> writes: >> ... rather it happens because the CREATE INDEX command >> deliberately loads the index leaf pages only 2/3rds full, to avoid a >> disproportionate amount of page splitting when normal inserts commence. > Interesting. Right after CREATE INDEX for a int4 c

Re: [GENERAL] Index size

> An other question: > > Is there any way to prevent duplicates on btree index attribute, > PERMITTING them on table? I can't think of any usefull usage for such an index. Can you explain why you need it? -- Tatsuo Ishii ---(end of broadcast)--- TI

Re: [GENERAL] Index size

> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > ... Now the number becomes 1967+7 = 1974. Still it's different from > > 2745. If you don't have deleted tuples, the difference probably comes > > from the fact that a btree index can never be 100% occupied. IMO > > 1974/2745 = 0.71 seems not so bad. >

Re: [GENERAL] Index size

Tatsuo Ishii <[EMAIL PROTECTED]> writes: > ... Now the number becomes 1967+7 = 1974. Still it's different from > 2745. If you don't have deleted tuples, the difference probably comes > from the fact that a btree index can never be 100% occupied. IMO > 1974/2745 = 0.71 seems not so bad. In fact the

Re: [GENERAL] Index size

Thanks a lot. An other question: Is there any way to prevent duplicates on btree index attribute, PERMITTING them on table? On Tue, 1 Mar 2005, Tatsuo Ishii wrote: > > I have created a btree index on a 'int4' attribute of a table. > > > > After i have inserted 1,000,000 raws in my table, i

Re: [GENERAL] Index size

> I have created a btree index on a 'int4' attribute of a table. > > After i have inserted 1,000,000 raws in my table, i can see that my index > size is 2745 Blocks (8KB each) from pg_class. That means about 21,960 KB > size. > > I try to understand hows is this number generated, because thought