Stack Overflow
Stack Overflow

Questions tagged [b-tree-index]

57 questions
32
votes
1 answer

How to lay out B-Tree data on disk?

I know how a B-Tree works in-memory, it's easy enough to implement. However, I don't know how to find a data layout that works effectively on disk, such that: The number of entries in the B-Tree can grow indefinitely (or at least to >…
Martin Häusler
  • 6,544
  • 8
  • 39
  • 66
32
votes
3 answers

Postgres usage of btree indexes vs MySQL B+trees

We are in the process of migrating from MySQL to PGSQL and we have a 100 million row table. When I was trying to ascertain how much space both systems use, I found much less difference for tables, but found huge differences for indexes. MySQL…
Greedy Coder
  • 1,256
  • 1
  • 15
  • 36
8
votes
3 answers

Finding the height of the B-Tree of a table in SQL Server

Since database data is organized in 8k pages in a B-tree, and likewise for PK information information, it should be possible for each table in the database to calculate the height of the B-Tree. Thus revealing how many jumps it takes to reach…
Carlo V. Dango
  • 13,322
  • 16
  • 71
  • 114
7
votes
1 answer

PostgreSQL daterange not using index correctly

I have a simple table which has a user_birthday field with a type of date (which can be NULL value) CREATE TABLE users ( user_id bigserial NOT NULL, user_email text NOT NULL, user_password text, user_first_name text NOT NULL, …
Shahar Hadas
  • 2,641
  • 1
  • 27
  • 29
7
votes
1 answer

Which index should I use on binary datatype column mysql

I am writing a simple tool to check duplicate files(i.e. files having same data). The mechanism is to generate hashes for each file using sha-512 algorithm and then store these hashes in MYSQL database. I store hashes in binary(64) unique not null…
Yogesh R.L
  • 609
  • 7
  • 17
6
votes
1 answer

Postgres not using index for date field

I have created index like CREATE INDEX bill_open_date_idx ON bill USING btree(date(open_date)); and, Column | Type open_date | timestamp without time zone And explain analyse are as follows CASE 1 explain analyze select * from…
Mohamed Anees A
  • 4,119
  • 1
  • 22
  • 35
6
votes
5 answers

database index: why pairing

I have a table with multiple indexes, several of which duplicate the same columns: Index 1 columns: X, B, C, D Index 2 columns: Y, B, C, D Index 3 columns: Z, B, C, D I'm not very knowledgeable on indexing in practice, so I'm wondering if somebody…
aw crud
  • 8,791
  • 19
  • 71
  • 115
4
votes
2 answers

Postgres text search with GIN index and sorted DESC on other column

I'm currently working on a search feature that ends up hitting the db with a LIKE query. It used to be of the form WHERE some_id = blah AND some_timestamp > blah AND (field1 LIKE '%some_text%' OR field2 LIKE '%some_text%' OR ...) ORDER BY…
user3112658
  • 121
  • 2
  • 8
3
votes
3 answers

Trivial question about max number of distinct values in a B-tree index

I am trying to learn about indexing. I looked at the actual indexes used in the database at work. I looked in to two random indexes: SELECT INDEX_NAME, INDEX_TYPE, LEAF_BLOCKS, DISTINCT_KEYS FROM ALL_INDEXES WHERE TABLE_NAME = 'TRANS' AND…
AlmostSQLxprt
  • 33
  • 3
3
votes
1 answer

How is PostgreSQL citext stored in a b-tree index? Lower case or as it is?

I am using citext in PostgreSQL for all text column types. I wonder about citext performance. I performed simple WHERE statement benchmarks over text columns that have a b-tree index, but I couldn't see any differences in terms of query cost. For…
fatih kosal
  • 175
  • 1
  • 11
3
votes
0 answers

Postgresql BTREE index row size limitation

Apparently there is row size limit (in bytes) for B-tree index in Postgresq, which causes index creation fail if string is too big. How to create partial index which would overcome this ugly limitation? Generally, I have two ideas: A) to store…
zlatko
  • 596
  • 1
  • 6
  • 23
3
votes
1 answer

How do I index variable length strings, integers, binaries in b-tree?

I am creating a database storage engine (for fun). I know it uses b-trees (and stuff), but in all of b-tree base examples, it shows that we need to sort keys and then store it for indexing, not for integers. I can understand sorting, but how to do…
00imvj00
  • 665
  • 7
  • 18
3
votes
1 answer

Postgresql not using multi-column indexes (btree_gin)

I am having issues making postgres use my multi-column indexes for full search using the btree_gin extension. This is for a search page for articles. The idea behind the use of btree_gin is to be able to get the 'id' field for sorting and…
tdma
  • 182
  • 1
  • 12
3
votes
0 answers

Postgresql Stored Function sometimes executes very slowly

We have a pretty big plpgsql function with an if- and elsif-statement in PostgreSQL 9.4.4 Inside every if-body there are function calls to stable-sql functions. We call the function in the following way: SELECT * from…
Thomas
  • 41
  • 4
3
votes
2 answers

Why MongoDB cannot use a compound index that is much similar(not exact) to the query?

Consider the below Mongo index strategy and the query, Index: db.collec.ensureIndex({a:1,b:1,c:1}); Query: db.collec.find({"a":"valueA"},{"_id":0,"a":1,"c":1}).sort({"c":-1}).limit(150) The explain on the above query returns: /* 0 */ { …
vivek_jonam
  • 3,237
  • 8
  • 32
  • 44
1
2 3 4