Linked Questions

83
votes
3answers
29k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
38
votes
2answers
32k views

Configuring PostgreSQL for read performance

Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow. The primary key (constraint) structure is similar ...
26
votes
4answers
3k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
29
votes
2answers
26k 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 one ...
15
votes
3answers
3k 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 ...
5
votes
3answers
4k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
9
votes
1answer
3k views

Enforcing constraints “two tables away”

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short for ...
8
votes
4answers
614 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
3
votes
2answers
2k views

How does Oracle handle composite index lookups?

MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix: MySQL cannot use the index to perform lookups if the columns do ...
5
votes
2answers
874 views

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description -----------+---------...
6
votes
1answer
1k views

Why does `SELECT count(*)` return 1?

Some RDBMS's seem to allow a query without a FROM clause, eg: postgres=# SELECT 'foo' bar; ┌─────┐ │ bar │ ├─────┤ │ foo │ └─────┘ But for those that allow it, why would SELECT count(*) not return 0?...
2
votes
1answer
3k views

how does multiple column index work in details

if we have a table T1 with columns A,B,C,D,E and an index ( A,B,C) built for it if we a SQL query joining on columns A,B or A,B,C or A, this index can still be used, but if the query is joining on B ...
4
votes
4answers
242 views

How many B-tree indexes do I need to support lookups by any subset of n columns?

First, some background ideas Generally, an SQL database which supports multi-column B-tree indexes also supports doing a lookup by a subset of the columns in the index if and only if they're the ...
2
votes
1answer
1k views

Searching a big table for a few results without scanning the whole table

I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) ...
5
votes
2answers
731 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...

15 30 50 per page