Linked Questions

109
votes
3answers
42k 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? ...
48
votes
2answers
43k 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 ...
38
votes
2answers
36k 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 ...
29
votes
4answers
4k 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:...
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 ...
13
votes
1answer
5k 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
783 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 ...
6
votes
3answers
5k 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 - ...
6
votes
1answer
2k 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?...
5
votes
3answers
4k views

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

SELECT something FROM table WHERE primary_key = ? vs. SELECT something FROM table WHERE primary_key = ? AND other_key = ? Say that this is a scenario where the inclusion of other_key does NOT ...
5
votes
2answers
1k 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 -----------+---------...
5
votes
2answers
1k 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 ...
4
votes
4answers
505 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 ...
4
votes
1answer
498 views

Why is a GiST index used for filtering on non-leading column?

I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query....
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 ...

15 30 50 per page