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 abo…
Tag: indexing
SQLalchemy specify which index to use
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 that I have and it doesn’t use it. Thanks! Answer I think you can use with_hint() for …
Does PostgreSQL support “accent insensitive” collations?
In Microsoft SQL Server, it’s possible to specify an “accent insensitive” collation (for a database, table or column), which means that it’s possible for a query like to find a row with a Joao name. I know that it’s possible to strip accents from strings in PostgreSQL using the u…
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 …
Index for nullable column
I have an index on a nullable column and I want to select all it’s values like this: In the explain plan I see a FULL TABLE SCAN (even a hint didn’t help) Does use the index… I googled and found out there are no null entries in indexes, thus the first query can’t use the index. My ques…
How can I see the contents of an Oracle index?
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 …
How to store ordered items which often change position in DB
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 pr…
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’…
Clustered index – multi-part vs single-part index and effects of inserts/deletes
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 primary key or index?
When should I use a primary key or an index? What are their differences and which is the best?