Tagged Questions
3
votes
2answers
42 views
Pagination with PostgreSQL 9.3: counting number of pages
I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.
-- index
create index category_score_id on products(category, score, ...
6
votes
2answers
83 views
Index that is not used, yet influences query
I've got a PostgreSQL 9.3 table with some numbers and some additional data:
CREATE TABLE mytable (
myid BIGINT,
somedata BYTEA
)
This table currently has about 10M records and takes 1GB of ...
2
votes
1answer
37 views
Why is my composite index not used for this table?
I am working with a fairly complicated schema, but I've simplified it for the purposes of this question.
Table "public.scheduler_appointment"
Column | Type ...
1
vote
0answers
47 views
Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?
Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
2
votes
0answers
61 views
Fast hamming distance queries in postgres
I have a large database (16M rows) containing perceptual hashes of images.
I'd like to be able to search for rows by hamming distance in a reasonable timeframe.
Currently, as far as I properly ...
3
votes
1answer
65 views
Very large btree index with few rows (openstreetmap gis data)
I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
0
votes
1answer
22 views
Creating unique constraint to be validated from input
I have this table name tbl with this table definition:
create table tbl (
id serial primary key not null,
counter integer not null default 1,
ref_id integer not null,
date_booked date ...
1
vote
1answer
99 views
Very slow simple PostgreSQL query on RDS
I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).
This is across a table of 4,152,928 rows..
select sum(some_field) c
from pages
where pages.some_id=123
and ...
0
votes
1answer
45 views
Postgres table growing on massive updates
I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing ...
0
votes
1answer
24 views
Can PostgreSQL index calculations be multithreaded?
During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time.
Is there any way to calculate indexes multithreaded for single ...
1
vote
3answers
279 views
What's the most efficient UUID column type
For storing a 128 bits UUID there are multiple storage options:
a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if ...
2
votes
1answer
112 views
Optimal hash technique to index large text
Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths.
The upper limit for the insertion rate is tens of billions of rows per ...
2
votes
1answer
68 views
Character varying index overhead & length limit
I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.
I have read about B-Trees, but I can't determine how much disk ...
0
votes
1answer
21 views
PostgreSQL: How does the implicit index work when using “COPY” for loading bulk rows into a newly created table?
What I know for fast loading bulk rows into a new table is:
Create the table(without creating the index)
Use "COPY" to load data from a file into the table
Create the index
What if I added a ...
1
vote
0answers
37 views
Guidelines on best indexing strategy for varying searches on 20+ columns
I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.
Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.
The problem:
...
1
vote
1answer
36 views
Can an index refer to only one or a subset of possible values?
I am using Postgres. The table looks something like this:
tran_id SERIAL PRIMARY KEY,
account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE,
...
2
votes
1answer
194 views
Create index on very large table with many shared values
I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values.
Table schema looks like:
Column | Type | Modifiers | Storage | Stats ...
2
votes
1answer
142 views
Postgresql not using GIN trigram index when performing non-ASCII LIKE query?
Steps to reproduce
Create database
CREATE DATABASE citiesdb
WITH OWNER = citiesowner
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
...
6
votes
2answers
274 views
Improve performance of COUNT/GROUP-BY in large PostgresSQL table?
I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
0
votes
1answer
62 views
How to index WHERE (start_date >= '2013-12-15')
I have a table named items, and inside the table there is a some_date column, with date as the datatype.
I was wondering how can I index the some_date column with PostgreSQL
SELECT "items".* FROM ...
3
votes
2answers
108 views
Compound Index on 43 Million PostgreSQL table
This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order)
Rather than overload that question, I figure I can sharpen and limit my ...
1
vote
1answer
102 views
Spliting Datetime into 2 Columns for Restaurant Reservation Schema?
Using PostgreSQL.
The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
10
votes
3answers
470 views
Indexes for SQL query with WHERE condition and GROUP BY
I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow.
My query:
SELECT group_id
FROM counter
WHERE ts between ...
1
vote
2answers
71 views
Should I create an index for non key columns?
I have a table in a PostGreSql database defined as following:
CREATE TABLE public."MATCH"(
"ITEM_A_ID" bigint DEFAULT 0,
"ITEM_B_ID" bigint DEFAULT 0,
"OWNER_A_ID" bigint DEFAULT 0,
...
2
votes
2answers
148 views
Postgres point() index not being used
\d aggregate
Materialized view "public.aggregate"
Column | Type | Modifiers
------------------+-----------------------------+-----------
...
0
votes
1answer
126 views
PostgreSQL - Return unique combinations of columns based on where clause
This is quite a long question, please bear with me.
So I'd like to first explain I have a database of firewall logs created using the following command:
CREATE TABLE firewall_logs_mapped
(
...
1
vote
1answer
99 views
Optimizing Postgres query
I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.
I've got two indexes on addresses table :
Index on state field
...
3
votes
1answer
155 views
2 B-tree indices OR 1 GiST index on tsrange — which will perform better?
I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
2
votes
2answers
112 views
Most performant way to fetch last record
I have a Postgres table Prices with the columns
price (Decimal)
product_id (Int)
Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
1
vote
1answer
34 views
Understanding the output of \d <table> in psql
Say I run the following command:
\d mtb.big_table
I get the output shown below. My questions are:
What are column modifiers? (what does default now() mean?)
Under Indexes, Why do some indices ...
0
votes
0answers
212 views
Yet another slow query Postgresql 9.3
I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
1
vote
1answer
42 views
What indexes would I create for a query like this?
Here's a query I've got:
SELECT "balance_transactions"."fee"
FROM "balance_transactions"
JOIN charges ON balance_transactions.source = charges.balance_id
WHERE "balance_transactions"."account_id" = ...
0
votes
1answer
269 views
PostgreSQL FTS and Trigram-similarity Query Optimization
I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
1
vote
1answer
168 views
Postgres - Optimizing an view dependent on an aggregate function?
I have a table of about 15 million records. Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes. This results in multiple rows ...
2
votes
1answer
493 views
How to create an index on an integer json property in postgres
I can't figure out for the life of me how to create an index on a property of my json column which is an integer.
I tried it this way (and also dozens of others)
CREATE INDEX user_reputation_idx ON ...
4
votes
1answer
669 views
Why would you index text_pattern_ops on a text column?
Today Seven Databases in Seven Weeks introduced me to per-operator indexes.
You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
2
votes
1answer
478 views
Need for indexes on foreign keys
I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.
If I have two tables, both of them have an integer as a primary key.
The first table references ...
0
votes
1answer
64 views
Constrain exclusion to fetch latest N rows
I'm managing a table with several million records which are being inserted in real time.
A part of my app needs to display the last N inserted records, so at the beginning I just queried for:
...
1
vote
2answers
442 views
Postgresql indexes in memory
I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes ...
0
votes
1answer
134 views
Best read performance when every query is known, and the data doesn't change
I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
5
votes
2answers
2k views
Efficient query to get greatest value per group from big table
Given the table:
Column | Type
id | integer
latitude | numeric(9,6)
longitude | numeric(9,6)
...
0
votes
3answers
380 views
Performance of primary key IDs with gaps (but in sequential order)
I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e:
154300000
283700000
351300000
464200000
...will ...
0
votes
1answer
85 views
How to store values of all columns in an index in PostgreSQL?
As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes indexes do not store the entire row data themselves. Is it possible to store the whole row ...
1
vote
1answer
113 views
Benefit to keeping a record in the database, rather than deleting it, for performance issues?
So I have a client that I am building a Rails app for....I am using PostgreSQL.
He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
1
vote
2answers
257 views
Optimize simple query using ORDER BY date and text
I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records
The data types of the table are:
fcv_id = serial
fcv_fecha_comprobante = ...
0
votes
1answer
130 views
How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?
I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:
add_index :users, [:username, :created_at]
That should ...
1
vote
1answer
350 views
Index for speeding up sorting by boolean, timestamp
For a website that displays recent news, I have this query on the home page:
SELECT *
FROM "cms_news"
WHERE
NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND
...
2
votes
0answers
249 views
PostgreSQL index array of int4range using GIN / GIST - custom operator class
Here is my table:
CREATE TABLE
mytable
(
id INT NOT NULL PRIMARY KEY,
val int4range[]
);
I want to index the val column:
CREATE ...
2
votes
2answers
189 views
Adding an index to a system catalog in Postgres
I'm having a situation very similar to the one described here:
I've got a SaaS situation where I'm using 1000+ schemas in a single
database (each schema contains the same tables, just different ...
1
vote
2answers
464 views
Avoiding a sort on an already clustered index for group by
On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid:
select count(did), pid
from T
group by pid
Here is the query plan:
...
