Tagged Questions
1
vote
2answers
21 views
Slow query where index isn't used
Overview
I've been working on Netdot trying to speed up some of the queries. Some of them can benefit from SQL changes because of unneeded joins or broad searches, but some have proven harder to ...
38
votes
7answers
31k views
Show which columns an index is on in PostgreSQL
I would like to get the columns that an index is on in PostgreSQL.
In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column.
mysql> show indexes from foos;
...
1
vote
1answer
32 views
How to optimize query by index PostgreSQL
I want to fetch users that has 1 or more processed bets. I do this by using next sql:
SELECT user_id FROM bets
WHERE bets.state in ('guessed', 'losed')
GROUP BY user_id
HAVING count(*) > 0;
...
0
votes
0answers
43 views
Why postgres 9.3 table keeps losing the primary key and indexes?
In pg 9.3 database for rails 3.2 app, table engine_configs keeps losing primary key (id) and its indexes. Here is the table:
For the table shown above, we just added the primary key again and it is ...
1
vote
1answer
57 views
Query by coordinates takes too long - options to optimize?
I have a table where I store events (about 5M at the moment, but there will be more). Each event has two attributes that I care about for this query - location (latitude and longitude pair) and ...
0
votes
1answer
23 views
Index Structure in postgreSQL
After creating an index in pgAdmin, is it possible to view the structure of the index in order to see how the column (s) have been sorted? I want to be able to see the B tree data structure. Thanks ...
1
vote
1answer
29 views
Rails DB Indexing: Is this an anti-pattern?
Would setting up indexes like this in a Rails 4 app (using Postgresql) be considered okay if I think the queries my app runs warrants them? Just checking as I am not an index pro and I don't want to ...
-2
votes
1answer
38 views
Create new column with an index based on column with dates in SQL
EDIT: Indeed, order was wrong.
I'm pretty new to SQL and I use pgAdminIII for it. In our DB we store reports with different dates. For every person we have around 10 reports. In order to compare only ...
0
votes
2answers
31 views
Converting MySQL indexes with duplicate names to PostgreSQL
I'm converting some CREATE TABLE queries from MySQL to Postgres and have encountered an issue with creating multiple keys (aka Indexes) with the same name on different tables.
For example, in MySQL ...
0
votes
1answer
25 views
Error creating an index on postgresql with desc
When executing the following on postgresql 8.2:
CREATE INDEX product_index_8 ON product (product_id DESC, naam DESC, verbruik_per_eenheid DESC, inhoud DESC, barcode DESC);
We get the error:
...
0
votes
0answers
43 views
postgresql create index on join table/nested query to make it fast
postgresql(version 1.18.1)
create index on join table/ or the way below is nested query
i have two table here:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL ...
9
votes
3answers
7k views
Full-text search in CouchDB
I have a problem and hope to get an answer from you :-)
So, I took geonames.org and imported all their data of German cities with all districts.
If I enter "Hamburg", it lists "Hamburg Center, ...
0
votes
2answers
23 views
Slow index scan
I have table with index:
Table:
Participates (player_id integer, other...)
Indexes:
"index_participates_on_player_id" btree (player_id)
Table contains 400kk rows.
I execute the same query two ...
0
votes
0answers
58 views
How can I speed up Postgres performance of Django's User lookup query
My Django site is starting to run sluggishly as the number of users scales up. New Relic tells me that the queries which consistently take the longest are the User lookup queries:
SELECT ...
0
votes
1answer
25 views
speed up GIN index creation
I'm trying to create a GIN index on an hstore column, avg 3KB per row and 2.5M rows. It takes ages (I gave up after 40 minutes). Gist comes back in couple of minutes. Any way to speed this up (or any ...
0
votes
1answer
21 views
Index method for a column used only for ordering
I have a table product_images with a foreign key product_id and integer field order to manualy set order of product's images. Knowing that the table will be used only like this:
SELECT * FROM ...
4
votes
2answers
70 views
Postgres returns records in wrong order
I have pretty big table messages. It contains about 100mil records.
When I run simple query:
select uid from messages order by uid asc limit 1000
The result is very strange. Records from the ...
1
vote
3answers
30 views
Will combined index work for queries on only one column?
I am using PostgreSQL. I am not sure that if I have a combined index, say on column A and B, when I query only of B, will the index be used?
4
votes
2answers
4k views
PostgreSQL Multi-Column index join with comparison (“<” and “>”) operators
I'm trying to take advantage of a multi-column btree index in PostgreSQL to perform an annoying join between two tables.
Table "revision_main"
Column | Type ...
1
vote
2answers
93 views
Rails 3 Postgres uses single field index for query, but shouldn't it use the compound index?
So User has many :orders, which works like you expect. I also have a valid scope on order that should filter by ensuring the orders are in a set of whitelisted states (not canceled orders, for ...
0
votes
0answers
42 views
Postgresql Suggest Indexes
In Microsoft SQL Server Management Studio, when you run SHOWPLAN on a query, it will sometimes tell you what indexes you should create in order to speed up the query.
I am using Postgresql, and can't ...
1
vote
2answers
27 views
Optimal use of indexes for aggregate queries
My Postgres table looks something like this:
tran_id SERIAL PRIMARY KEY,
acct_id int NOT NULL,
tran_type char(2) NOT NULL,
/* some performance irrelevant fields */
The only multi-row query the ...
12
votes
1answer
4k views
Index for finding an element in a JSON array
I have a table that looks like this:
CREATE TABLE tracks (id SERIAL, artists JSON);
INSERT INTO tracks (id, artists)
VALUES (1, '[{"name": "blink-182"}]');
INSERT INTO tracks (id, artists)
...
2
votes
3answers
66 views
Rails what's difference in unique index and validates_uniqueness_of
Firstly, can anyone explain how unique index works in databases?
Suppose I have a User model with a name column and I add a unique index on it but in the model (user.rb) I just have a presence ...
2
votes
0answers
96 views
Why does add_index using 'gin' create a 'btree' index instead?
I am on PostgreSQL 9.3.4 and Rails 4.0.4.
I add a "tags" column, and corresponding gin index (or, at least I ask for one).
class AddTagsToPhotos < ActiveRecord::Migration
def change
...
1
vote
1answer
26 views
How do I fix a PG::ProgramLimitExceeded: ERROR?
I created a table answers and added the following index to it:
add_index :answers, [:output, :question_id], unique: true
The index allows for a fast lookup of an answer by it's output and ...
2
votes
0answers
97 views
Multiple ways to create index on a json field's nested property in PostgreSQL 9.3
In PostgreSQL 9.3, there are multiple ways to build an expression, which points to a json field's nested property:
data->'foo'->>'bar'
data#>>'{foo,bar}'
json_extract_path_text(data, ...
0
votes
1answer
24 views
Unique index on text column - what is the index type?
In my table I have column slug of type TEXT. It will store slug that will be used for queries like this:
SELECT * FROM posts WHERE slug = 'my-post-slug'
For now I set up unique index on slug.
...
1
vote
2answers
54 views
How to force PostgreSQL to use my index?
CREATE TABLE product (
product_id SERIAL,
factory_key VARCHAR(60),
relevant BOOLEAN
)
Indexes:
"product_factory_key_key" btree (factory_key);
"product_factory_key_relevant_key" ...
4
votes
2answers
79 views
Why is Postgres scanning a huge table instead of using my index?
I noticed one of my SQL queries is much slower than I expected it to be, and it turns out that the query planner is coming up with a plan that seems really bad to me. My query looks like this:
select ...
0
votes
1answer
41 views
Why is select faster than a function call?
I have a STABLE function get_value_by_id(id) which just gets the value from table indexed by id.
I run EXPLAIN ANALYZE on function and also on the same SELECT statement which is inside function.
...
0
votes
1answer
20 views
Why postgres is not using the index in my query
I have 2 tables as follow:
tb_st:
Columns:
st_id | integer
st | character varying(80)
type | integer
Indexes:
PRIMARY KEY (st_id)
UNIQUE INDEX (st, type)
INDEX (st)
tb_pd:
Column
...
0
votes
1answer
29 views
Rails + validates_overlap gem: When to add indexes to the database?
I'm using the validates_overlap gem (https://github.com/robinbortlik/validates_overlap) in a Rails app. Here is the Model code:
validates :start_time, :end_time, overlap: { scope: "device_id", ...
2
votes
1answer
20 views
PostgreSQL text range scan
I have written a query whose aim is to get 10 results including the current one, padding up to 9 entries on either side for an alphabetical list which can be sorted by the reciever.
This is the query ...
2
votes
1answer
70 views
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 ...
0
votes
1answer
37 views
How does LIMIT interact with DELETE by primary key in Postgres? (Fix corrupt unique index)
I've been handed a database that's stuck in a weird state. At some indeterminate time in the past, I ended up in a situation where I had duplicate rows in the same table with the same primary key:
...
0
votes
2answers
31 views
Impact of existing indexes on performance of COPY FROM
After reading the PostgreSQL reference doc on COPY, I did not find how COPY deals with indexes. What exactly happens when COPYing data from a file into a table when the table already has indexes. I ...
1
vote
1answer
59 views
What is the correct way to optimize and/or index this query?
I've got a table pings with about 15 million rows in it. I'm on postgres 9.2.4. The relevant columns it has are a foreign key monitor_id, a created_at timestamp, and a response_time that's an integer ...
1
vote
2answers
38 views
Are indexes on the join table used in many-to-many relationships?
Say I have a database with 3 tables describing bus timetables:
journey
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | ...
1
vote
2answers
15 views
Will a primary key index serve as an index for a foreign key when fk columns are subset of pk?
I have a table where part of the primary key is a foreign key to another table.
create table player_result (
event_id integer not null,
pub_time timestamp not null,
name_key ...
0
votes
0answers
59 views
Same postgres query producing different query plans in different databases
posgtres version 9.1.9
Following query produces different plan when run in two different databases.
explain (analyze,buffers) SELECT group_.groupid AS groupId,
group_.name AS ...
7
votes
1answer
569 views
Postgres hstore: GIN vs GiST index performance
I have to decide whether to use GIN or GiST indexing for an hstore column.
The Postgres docs state:
GIN index lookups are about three times faster than GiST
GIN indexes take about three times ...
1
vote
1answer
103 views
how can I detect gin and gist
how can I detect GIN and GiST indexes in postgresql? I am looking for if an database of postgres use fulltext. I think that a table use GIN o GiST then is using fulltext.
I accept that GIN or GiST ...
7
votes
4answers
6k views
What's the difference between B-Tree and GiST index methods (in PostgreSQL)?
I have been working on optimizing my Postgres databases recently, and traditionally, I've only ever use B-Tree indexes. However, I saw that GiST indexes suport non-unique, multicolumn indexes, in the ...
23
votes
1answer
6k views
PostgreSQL: GIN or GiST indexes?
From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in ...
0
votes
0answers
69 views
Messages: Query a user's chats
For an app like iOS Messages:
How should I design the server database queries & schema?
Queries
Create a user.
Get a user's chats.
Get the messages between two users.
Add a message to a chat ...
1
vote
1answer
65 views
How to use uuid with postgresql gist index type?
I can't use directly uuid with gist index
CREATE INDEX idx_leaderboads_values_gist
ON leaderboard_entry
USING gist
(id_leaderboard , value);
And I got this error:
ERROR: data type uuid ...
1
vote
2answers
58 views
Multicolumn index on 3 fields with heterogenous data types
I have a postgres table with 3 fields:
a : postgis geometry
b : array varchar[]
c : integer
and I have a query that involves all of them. I would like to add a multicolumn index to speed it up ...
0
votes
3answers
946 views
PostgreSQL index not used for query on range
I'm using PostgreSQL (9.2.0) and have a table of IP ranges. Here's the SQL:
CREATE TABLE ips
(
id serial NOT NULL,
begin_ip_num bigint,
end_ip_num bigint,
country_name character varying(255),
...
1
vote
1answer
27 views
Non-selective partial index on nulls
I've just found an interesting partial index in my db:
CREATE INDEX orders_idx
ON orders
USING btree
(status)
WHERE status IS NULL;
As you see it's completely non-selective and imho ...
