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

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,

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

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

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

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

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

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.

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

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

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?

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): >

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 >

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 |

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

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:

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

[GENERAL] Index size

I'd like to understand a bit more about indexes in PG. When I have a row in a table, and an index, say, for a single column, does that duplicate the entire row on disk? Or is there some kind of id lookup involved? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

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

[GENERAL] Index Size

Hi, Cube code provided by postgres contrib folder. It uses the NDBOX structure. On creating index, it's size increase at a high rate. On inserting some tuple and creating indexes its behaviour is shown below. 1. When there is only one tuple select pg_size_pretty(pg_relation_

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

Re: [GENERAL] Index Size

Craig Ringer cr...@postnewspapers.com.au 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

Re: [GENERAL] Index Size

On Tue, May 31, 2011 at 8:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au 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.

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

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

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

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=9

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

[GENERAL] Index size

Hi, 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 that

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 that

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 can

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

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

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)--- TIP

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 column

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? Shoulda

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