New answers tagged

2 votes

Select function-based index data as rows in a query?

You can't, but you can select it from the table: select my_owner.endpoint_list(shape),objectid,rowid from my_owner.sidewalks;
user avatar
0 votes

How do I find out why CREATE INDEX CONCURRENTLY is failing?

Look into the database log. If you left the default configuration, you will see a log entry if CREATE INDEX was terminated by an error (for example, through statement_timeout). If it failed, remove ...
user avatar
  • 32.2k
-1 votes

Why is the query planner only using one column of two in the index?

Right now you have no index on col1 even though that's the first item in your where clause. You should definitely create an index on the aggregate of col1, archive and created_at. This should solve ...
user avatar
  • 109
1 vote

Non-clustered Primary Key and Clustered Index

if a Primary Key is non-clustered, does it store all the columns with it? Or only the Primary Key columns and the columns referencing the clustered index? The index type, clustered or non-clustered, ...
user avatar
  • 24.1k
4 votes
Accepted

Non-clustered Primary Key and Clustered Index

if a Primary Key is non-clustered, does it store all the columns with it? Nope, that is a characteristic of the clustered index, not the primary key. A nonclustered primary key will only store the ...
user avatar
  • 24.2k
1 vote

How to enforce only one NULL value per unique combination of some other columns?

I want to have only one null value for some_value when enabled is false. A minimalist partial unique index can do that: CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1) -- constant ...
user avatar
0 votes

Should I be using a non-clustered primary key on multiple columns?

The primary key should be based on business logic - how do you uniquely identify a row in your table? Probably ServiceLocation, MeterNumber, IntervalDay, IntervalHour, that's most of your columns so I ...
user avatar
  • 2,299
0 votes
Accepted

How to enforce only one NULL value per unique combination of some other columns?

From this constraint: CHECK ( (enabled IS FALSE AND some_value IS NULL) OR (enabled IS TRUE AND some_value IS NOT NULL)); ...the "enabled" column is redundant and simply means &...
user avatar
  • 437
1 vote
Accepted

Forcing a sequential scan over the index scan

set enable_indexscan TO off; set enable_bitmapscan TO off; solves
user avatar
  • 95
1 vote

The implications of lack on indexes on supporting a large production database

As J.D. has mentioned indexes (or lack of) can have a significant impact on query level performance and it certainly warrants further investigation. In a nutshell the more that SQL can efficiently use ...
user avatar
2 votes

The implications of lack on indexes on supporting a large production database

Typically it's a good idea to index on the fields in the predicates (JOIN, WHERE, and HAVING clauses) of the queries commonly executed on your server or the queries that require executing with the ...
user avatar
  • 24.2k
0 votes
Accepted

Check how many Bytes does my index need to be stored?

pg_indexes_size returns the size of all indexes on the table. To get the size of the primary key index alone, you first have to find its name (probably customer_pkey). Then you can run SELECT ...
user avatar
  • 32.2k
0 votes
Accepted

Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table

Perhaps the setting for tmpdir is pointing into a small disk partition. Check SHOW VARIABLES LIKE 'tmpdir';
user avatar
  • 68.1k
3 votes

how to find out the `Key Lookups` in the current query plan?

You can use XQuery to filter the XML plan. For example: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') .......... .......... WHERE er.session_id <> @@...
user avatar
  • 5,214
0 votes

MySQL Indexes on all column combinations

Miscellany Tips Do not blindly add indexes. Often they will go unused for mysterious reasons. Do list the important queries, then make indexes for them. Eliminate duplicates. (Give me the list, I'...
user avatar
  • 68.1k
10 votes

how to find out the `Key Lookups` in the current query plan?

This is not a good idea. Take it from me. A long time back I coded a check into sp_BlitzCache that found plans with Key Lookups in them, and then compared that operator's cost to the total cost of the ...
user avatar
  • 33.4k
2 votes

Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)

Looks like the visibility map gets out of date quickly, requiring the engine to visit data pages even during the index-only scan to confirm visibility of each row. May be you need to make autovacuum ...
user avatar
  • 21.2k
0 votes

The UPDATE queries is 50x slower than its SELECT query

This might not be the answer you are looking for, but why not use a composite key for the queue_id, generated from club_id and user_id? You can even add it to your table like this: alter table ...
user avatar
  • 346
0 votes

The UPDATE queries is 50x slower than its SELECT query

It seems like this would do the job much faster: UPDATE sync_read SET sync_read.queue_id = FLOOR(RAND() * POW(10,6)) WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51'; That ...
user avatar
  • 68.1k
-3 votes

The UPDATE queries is 50x slower than its SELECT query

very nice question, as you know the write speed is always slower than read spead when you select data you are reading from your hard drive, but when you update, it's like you're fetching for the ...
user avatar
0 votes

Simple query with a single join very slow

If you were to upgrade to at least MySQL 8.0, you'd be able to take advantage of lateral joins: create index order_ordered on order (company_id, id desc); SELECT lateral_o.id, lateral_o....
user avatar
  • 2,299
1 vote

how to properly index a varchar column that won't use LIKE

(Focusing on the Title) LIKE is useful in testing VARCHARs. x LIKE 'abc' -- this turns in to x = 'abc' x LIKE 'abc%' -- will _probably_ use INDEX(x) x LIKE '%abc' -- cannot use INDEX(x) x = 123 ...
user avatar
  • 68.1k
2 votes

New Index creation can benefit from existing Index

The creation of the composite index will not use the existing index, you have to obtain the additional column from the table and sort with the other column. It will (almost always) be faster to read ...
user avatar
  • 2,299
2 votes
Accepted

how to properly index a varchar column that won't use LIKE

Given your query, I would add this index: ALTER TABLE pdf_packages ADD INDEX (private_reference_number, deleted_at); That matches the two conditions you have in your WHERE clause, so it should do the ...
user avatar
  • 9,313

Top 50 recent answers are included