Tag Info

New answers tagged

1

Columns specified in WHERE and JOIN clause predicates are the candidates for index keys. ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows. Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the ...


2

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


2

Too bad If you cannot change the query at all, that's too bad. You won't get a good solution. If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see below) with the same name in another schema (or just a temporary one) and adapt the search_path (optionally just in sessions where this is desirable) - for hugely ...


1

You could try creating an INSTEAD OF SELECT rule on the table .. although this might break the application (depending on what all actually uses the table in question) CREATE RULE "RETURN_MODIFIED_SELECT" AS ON SELECT TO run.frames_stat DO INSTEAD <MY QUERY FROM BELOW>; I have not used RULEs that much, personally - so I may have this ...


2

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course. No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index. No, I don't think your query should take ...


1

I think it's safe to remove the non-primary key as it's an exact duplicate of the primary key. If one of them is used more and takes less space, did you check the fragmentation and last statistics date on both? Maybe one of them is more fragmented or has better stats.


1

I have a big Index Organized Table (IOT) with 74 columns Why do you think that this table is a candidate for an IOT? IMHO, IOT's are meant to be narrow tables that don't change often. I have done testing with narrow tables and have found IOT's to do inserts at a slower rate than a traditional table with indexes. You may want to start by recreating this ...


0

You have the best index there is. It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data. (To further address all the comments...) Note that it needed to read about 200K rows (of the index) to do the count. That many rows takes time. INDEX(offer_id, ...


1

It depends. Prefix indexing INDEX baz(800) is rarely useful. MyISAM and InnoDB work dramatically differently in the area you are in. In InnoDB, if id is the PRIMARY KEY, it is almost useless to have an index starting with id. This is because the PK is clustered with the data. For MyISAM, the PK is a separate BTree, so the analysis is quite different. If ...


0

WHERE playable_character = 0 AND date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now() Start with the "=" item, then do the range: INDEX(playable_character, date_published); "Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you ...


3

You assumption about adjacency is correct. If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between ...


3

Very short version: Yes, sometimes. PostgreSQL can use bitmap index scans to combine multiple indexes. A predicate like WHERE a > 50 AND a < 50000 is a specialisation of the more general form: wHERE a > 50 and b < 50000 for a = b. PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It ...


2

When considering the impact of new index, the important things to think are how often rows are added to the table, is the indexed field getting updated (often) and how many distinct values there are in that field (selectivity). For table this size, it's probably not going improve the performance that much, but if you're getting deadlocks it might help. If ...


4

In short: No. There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure. When the index name is ...


0

The default operator class for GIN indexes on json columns jsonb_ops only supports these operators (per documentaion): Name Indexed Data Type Indexable Operators ... jsonb_ops jsonb ? ?& ?| @> I don't have a pg 9.4 instance to test right now. but I would try the other way round: Create a simple IMMUTABLE SQL function ...


3

You're confusing a few terms, I think. The ndf file is not an "index file", it is just another database data file to house the data. It sounds like you have two database data files (the MDF and NDF). With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the ...



Top 50 recent answers are included