Tagged Questions
1
vote
1answer
13 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
1
vote
2answers
26 views
Why PostreSQL 9.2 does not use index scan if it can?
My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.
Here is my table. The index I am talking about is name_left_prefix
cwu=# \d web_city;
...
3
votes
3answers
125 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
5
votes
2answers
2k views
How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?
I may be asking the wrong question in the title. Here are the facts:
My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
4
votes
2answers
104 views
Inner join using an array column in PostgreSQL
Having trouble indexing and executing a query in O(log n) time.
The query includes an inner join, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
1answer
159 views
Copy Postgres databases with indexes between instances
How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes?
We have an application we deploy which uses Postgres 8.4.7 database in Linux. ...
2
votes
1answer
34 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
4
votes
1answer
64 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
2answers
101 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
1answer
77 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
1answer
650 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
3
votes
3answers
192 views
PostgreSQL 8.3: Slow GROUP BY on large table
I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...
select run_id, count(*) from result group by run_id;
I have an index ...
4
votes
1answer
84 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
1
vote
1answer
96 views
Use GIN to index bit strings
I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
4
votes
2answers
169 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
