Questions tagged [b-tree-index]
57 questions
0
votes
0 answers
Postgresql planner won't select index for 'NOT' queries
[Title updated to reflect updates in description]
I am running Postgresql 9.6
I have a complex query that isn't using the indexes that I expect, when I break it down to this small example I am lost as to why the index isn't being used.
These…
fei0x
- 4,259
- 4
- 17
- 37
0
votes
0 answers
Postgresql Index creation for Timestamp in a given Timezone
I want to create an index on a column which stores a timestamp inside a JSON value in Postgres. The index needs to be converted to a timezone as well. This is the query I need to run,
SELECT count(*) FROM reservations
WHERE (((json #>> ‘{details,…
iamcoded
- 1
0
votes
1 answer
How does DynamoDB internal architecture look like?
How does the indexing maintained internally?
Does the partition behaves like a binary tree? How Database B-tree Indexing works - DZone Database
The Guts 'n' Glory of Database Internals: B+Tree - DZone Database
While partitioning the tables, does the…
Mukundhan
- 3,284
- 23
- 36
0
votes
1 answer
Postgres multi-column index is taking forever to complete
I have a table with around 270,000,000 rows and this is how I created it.
CREATE TABLE init_package_details AS
SELECT pcont.package_content_id as package_content_id,
pcont.activity_id as activity_id,
…
Mohamed Anees A
- 4,119
- 1
- 22
- 35
0
votes
1 answer
PostgreSQL index on multiple columns, when is it too much?
Using PostgreSQL 9.6
I have a table with some values I want to filter on and order by time:
a timestamp (may be range selected in the UI)
status string (only a few known values for now, also selectable in the UI)
context (scope of the data in the…
Christophe Roussy
- 16,299
- 4
- 85
- 85
0
votes
1 answer
Does SQLite support bulk-loading (sort-then-indexing)?
When constructing indexing tree from existing data, there is a bulk-loading algorithm, like
https://en.wikipedia.org/wiki/B%2B_tree#Bulk-loading
https://www.youtube.com/watch?v=HJgXVxsO5YU
When creating an index for a non-empty table, does SQLite…
Joe C
- 2,757
- 2
- 26
- 46
0
votes
1 answer
PostgreSQL Index physical layout
I am trying to understand how PostgreSQL physical index layout is. What I came to know is indexes are stores as part of set of pages with a B tree data structure. I am trying to understand how vacuumming impacts indexes. Does it help to contain its…
vishal
- 32
- 2
- 8
0
votes
1 answer
SQL Server 2008: number of pages in an index
I am attempting to investigate into the number of pages per index and have formulated the following query:
SELECT
pyi.index_type_desc as [index type],
i.name as [index name],
sum(pyi.page_count) as [number of…
Klaus Nji
- 18,107
- 29
- 105
- 185
0
votes
2 answers
logarithmic time count(*) range query on any DBMS
Suppose there is a table T, with column C indexed by a B-tree, and a given constant k. Assume the result of the following query would be n:
select count(*) from T where C > k;
I tried such a query in MySQL(InnoDB) ,with column C indexed by B-tree,…
Hamid Alaei
- 406
- 4
- 16
0
votes
1 answer
Hash Index based Database Engine in mySQL
MYSQL Server supports different database engine like InnoDB, ISAM, Memory etc.
InnoDB uses BTree while Memory uses Hashing for Indexing Purpose.
My Queries are simple(equality checking) so I don't need a Btree based Indexing so I am using 'Memory'…
mitesh
- 39
- 7
-1
votes
1 answer
MySQL calculate RAM B+Tree's footprint for a single table (comparison with python data-struct)
I have the below data that I am caching in Python now:
id timestamp data-string
The data-string size is ~87 bytes. Storing this optimally in python (using dict and having the timestamp pre-pended to the data-str with delimiter), the RAM costing…
Ethan
- 4,915
- 1
- 28
- 36
-1
votes
1 answer
B Tree and B+Tree Index diferencies
I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate it:
Why can I store more search keys on an B+ Tree Index? My guess would be that the reason is because the nodes…
David
- 1