New answers tagged

1

If I interpret your added information correctly, you have 21 distinct source_id with roughly 1 million rows each (divided pretty evenly among each source_id). That means your query counts roughly 3/4 of the whole table. An index typically can't buy much either way. Test with EXPLAIN (just EXPLAIN is enough for this purpose) to see the estimated cost for ...


1

MyISAM! Shame. Switch to InnoDB. Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc. But do make sure it is the 'appropriate' character set: VARCHAR(21) CHARACTER SET ascii If it is case sensitive, then add on COLLATE ascii_bin. Yes, there is a memory and disk penalty. But there are tradeoffs with speed, simplicity, etc. ...


1

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency ...


1

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you. Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes. You will have the added advantage of taking a bit of ...


0

Querying the table in any way takes eons. While queries are running against the table, the BCP processing basically stops. Its like all processing power goes to the query and I get a files backlog. How do I speed up query performance? I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted ...


7

I'm working on the assumption that the table in question is a fact table, not a dimension table with a huge composite key: Just to fix the performance issue in the short term, I would add all of these key columns as the table's clustered index, which means you won't have to INCLUDE a lot of measures and stuff, like the suggested index does. Also, make the ...


1

Assuming you want what your current query does (which seems different from what your description says). You ask: I think that problem is with filter but how to make it use index? Your query plan shows that Postgres is already using indexes for every step of the way. The added FILTER step only filters rows according to your additional predicates. I ...


2

Amending Paul's answer: You seem to be confused why an index that essentially copies the whole table can be beneficial. Indexes can be used to accelerate WHERE predicates and ORDER BY directives. The fact that your query plans have key lookups shows that the queries have at least a WHERE or an ORDER BY in them. It is not at all an egregious thing to do to ...


7

The missing index suggestions are opportunistic entries added whenever the optimizer happens to notice that an exact-match index for the current set of predicates it is considering do not exist on the base object. The information recorded in the DMVs is intended to be a helpful input to the normal activities of a skilled database tuner; it is not intended ...


1

DATE(added_time) = CURDATE() is the root of some of the evil Do not "hide" a column (added_time) inside a function (DATE) if want an index to be used. If added_time is a DATE, then you can simply do added_time = CURDATE() If it is a DATETIME or TIMESTAMP, then do added_time >= CURDATE() AND added_time < CURDATE() + INTERVAL 1 DAY Then your ...


1

Drastically reducing the number of rows will reduce the likelihood of getting the deadlock, but it won't go away completely. In simple terms the select is first using the index to determine the rows to select, then fetching the rows, while the insert is inserting a row, then trying to update the (XLOCKED) index. Application developers tend to use XLOCK ...


12

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) ...


2

Explain We would have to see table definition, cardinalities and the EXPLAIN output to be certain, but the reason is most likely this: Only your spatial GiST index on a.geom can be used. The btree index is not applicable. Postgres walks through the "closest" rows until it finds the first two matching your predicate. Normally, more restrictive conditions ...


2

Look at the names more carefully. pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing. The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.



Top 50 recent answers are included