In my schema.rb I have the following line:
add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree
When I run \di in psql I get:
Schema | Name | Type | Owner |…
I am trying to implement a database index based on the data structure (Blink tree) and algorithms suggested by Lehman and Yao in this paper. In page 2, the authors state that:
The disk is partitioned in sections of fixed size (physical pages; in…
I have big table with 22 millions records.
I want to execute next query:
select auto_alerts from alerts_stat where endDate > "2012-12-01"
To improve performance I added BTREE index for endData field:
CREATE INDEX endDate_index USING BTREE ON…
So I always thought that seeing "Using Where" in the Extra column was a good thing. However, I was planning a brown bag lunch for my coworkers on intepreting EXPLAIN and now I'm not so sure. The MySQL doc says this in the notes about "Using…
From time to time I encounter a strange MySQL behavior. Let's assume I have indexes (type, rel, created), (type), (rel). The best choice for a query like this one:
SELECT id FROM tbl
WHERE rel = 3 AND type = 3
ORDER BY created;
would be to use…
I am currently learning indexes in Oracle.
In Oracle documentation, following can be found :
Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is…
I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 million records).
I've estimated that this may be due…
It is usually said when you create indexes, it makes fetches faster for you but on the same time they slow down your updates (delete,insert,update) as with every update indexes are to be recreated.
I have a question in my mind: if database updates…
I am looking to compare indexes and further optimize my code. What I would like to do is force the query to run without an index so I can see what difference it has made. Is it possible to do this?
I have a simple count query that can use Index Only Scan, but it still take so long in PostgresQL!
I have a cars table with 2 columns type bigint and active boolean, I also have a multi-column index on those columns
CREATE TABLE cars
(
id BIGSERIAL…
When creating a model with a models.varchar(...) field, a varchar_pattern_ops index is being created.
This is the table generated in postgresql
Table "public.logger_btilog"
Column | Type | Modifiers…
I'm working in Sql Server 2005. I have an event log table that tracks user actions, and I want to make sure that inserts into the table are as fast as possible. Currently the table doesn't have any indexes. Does adding a single non-clustered index…
I access several tables remotely via DB Link. They are very normalized and the data in each is effective-dated. Of the millions of records in each table, only a subset of ~50k are current records.
The tables are internally managed by a commercial…
I'm trying to understand Cassandra's storage engine when it comes to composite columns. Unfortunately, the documentation I've read so far contains errors and is leaving me a bit blank.
First, terminology.
Composite columns comprise fully…
delayed_job does a query like this regularly:
SELECT "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2012-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2012-05-23 11:16:43.180841') OR locked_by = 'host:foo pid:1') AND…
