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
Is there a way in SQLalchemy to tell the query which index to use? The reason I need this is that the SQL queries it generates use the “wrong” index – there exists an index for exactly the two fields …
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
Is it possible to have a look at what is there inside an index using SQL*Plus? If I have a table like this: Table A ———————— rowid | id name 123 | 1 A 124 | 4 G 125 …
I need to be able to store a large list of ordered items in the DB. So far that’s straight-forward: In queries, I always need to get just a few items (filtered based on OtherFields) but in the correct order. Easy as well, putting an index on Position and using “order by Position”. Now the problem: Items change their Position
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 —
This question is about what happens with the reorganizing of data in a clustered index when an insert is done. I assume that it should be more expensive to do inserts on a table which has a clustered …
When should I use a primary key or an index? What are their differences and which is the best?
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.
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