Skip to content

Tag: indexing

Optimize BETWEEN date statement

I need help optimizing a Postgres query which uses the BETWEEN clause with a timestamp field. I have 2 tables: containing about 3394 rows containing about 4000000 rows There are btree indexes on both PKs id_one and id_two, on the FK id_one and cut_time. I want to perform a query like: This query retrieves about 1.700.000 rows in about 7

Creating index if index doesn’t exist

I have a problem creating an index with the advantage database server if it doesn’t exist with a sql query. My query looks like this: So I don’t use FullTextSearchIndizes,because it is a integer field. Otherwhise it would look like this: So, my only problem is how do I get the indices. I’ve read in other DBMS you can use

Indexes and multi column primary keys

In a MySQL database I have a table with the following primary key In my application I will also frequently be selecting on item by itself and less frequently on only invoice. I’m assuming I would benefit from indexes on these columns. MySQL does not complain when I define the following: But I don’t see any evidence (using DESCRIBE —

List columns with indexes 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. Does anything like this exist for PostgreSQL? I’ve tried d at the psql command prompt (with the -E option to show SQL) but it doesn’t show the information I’m looking for.

Force index use in Oracle

I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be better using the index, how could