Tag Info

New answers tagged

3

Erwin describes a method of creating an index that violates PostgreSQL's assumptions about the index, producing results that are likely incorrect and do not match the underlying table. That's one kind of corruption. Another kind of corruption is where an index has blocks that are simply invalid - zeroed out, replaced with random values, etc. Most likely ...


1

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though. For instance: lat | numeric lng | numeric So ...


0

Zerothly, you need to test your backups and have a recovery plan if your not doing that. Lets talk about some quick win performance first of all. Here are some options. Google can help flesh these out more. Are their multiple named instances on the box? Have you set max memory? Have you set the number of files for tempdb? If its at all possible, put the ...


3

Erwin's answer does a good job of answering the question as originally stated, however you added the comment: I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID' Assuming you are talking about the indisvalid column in pg_index, or some ...


3

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked: One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday: CREATE OR REPLACE FUNCTION f_fake_immutable_ts() RETURNS timestamp LANGUAGE ...


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


1

Perkinson's mtehodology merges logical(ideal) process model and logical(ideal) data model into one combined ideal model and calculates resources of each access path in a table. In this calculation, peak event frequency, entry point, data volume, cardinality, from entity, to entity, access type & required resources by each access, etc. are needed. ...


3

The index that will give you the most benefit is one on fldB, fldC, fldA desc or on fldC, fldB, fldA desc depending on your data. You'll need to check your data and keys to determine which column should go first. The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table ...


1

As only fldC & fldB are in the filter criteria, an index with these two columns on the left side will be right. Try to index on (fldC, fldB, fldA), this should do the trick mostly.


3

As you gave no details of what you want to accomplish to base any hints on, I can tell you only the generic stuff: If it is InnoDB then each secondary key contains the primary key values to find the right rows, so (a,b,d,e) is extended to (a,b,d,e,c) and (d,e) to (d,e,a,b,c) - as you can see, the columns indexed are the same but the order is not, and order ...


2

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use... and Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use? This is partially true. Index fragmentation wont cause HIGH CPU. Internal ...



Top 50 recent answers are included