Tag Info

New answers tagged

1

Two things that are very odd here: The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan. The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be ...


0

Query This query should be substantially faster in any case: SELECT parent_id, message_id, posted_at, share_count FROM messages WHERE feed_id = 7 AND posted_at >= '2015-01-01 4:0:0' AND posted_at < '2015-04-28 4:0:0' AND parent_id IS NULL -- match index condition UNION ALL ( SELECT DISTINCT ON(parent_id) parent_id, message_id, ...


1

It looks to me like you're querying lots of data in a big index, so it's slow. Nothing notably wrong there. If you're on PostgreSQL 9.3 or 9.4, you could try to see if you can get an index-only scan by making this into a covering index of sorts. CREATE INDEX idx_traffic_partner_only ON traffic (dt_created, clicks, impressions) WHERE campaign_id IS NULL ...


1

SQL Server query optimizer in this case will often use a table scan, depending on your data content. The alternative would be an index scan on your composite index followed by lookups for the found record to satisfy the select *. But you only have one extra column ("name") in the table compared to the index and the index has at least one hidden column, too ...


2

An index on (year, month, date) is essentially in time order. This will be useful for finding the 2014 records, but won't be good for finding all the March records. March 2012 won't be near March 2013, or March 2014. You'd be better off with two indexes - one like what you have, and one that starts with month. Then you could get two seeks. How the results ...


2

Here's something to test out. You can't force the order of execution in an OR, but you can in a CASE, so you can short-circuit extra processing if you put the most common scenario at the top of each CASE. EDIT: Since you aren't using any fields from orders (just testing that the order isn't deleted or in need of review), I moved that part to an EXISTS in ...


1

Without knowing details about exactly what type of queries are accessing that data and the indexes you are dealing with a precise answer is not possible. However, in general an index rebuild drops and then re-adds that index and locking needs to occur for that to happen. If you have queries that are attempting to access this table while the rebuild is going ...


1

You could use the RML utilities to capture a workload trace before and after the index rebuild and compare execution times. RML utilities come with a reporter application that can compare the workload analysis of two trace files. You can download it from here: X64: ...


1

In real world environments the situations where a keyword is encountered and then removed and will never be encountered again is quite low. The process for updating a record in the full text index process probably removes all previous references to the record then updates all new references. As such when the references to a keyword are removed it does not ...


0

Each run of this query will scan the whole table for two reasons: The index on name will not be used as the value starts with a wild char % You are not using the proper functions that utilize the fulltext index on the other two fields. Proposed solution: add a fulltext index on name field Use the proper functions to search. in this case, it is match : ...


0

SHOW CREATE TABLE -- need to see the PRIMARY KEY and the "index". 100-1000 is optimal (10K might be ok) for "batching" INSERTs -- That is, a single INSERT statement with 100 to 1000 rows in it. You have shown a single row being inserted. Will it work change to values ('a', 'b', 3), ('c', 'd', 4), ... That might give you a 10-fold speedup. That is also ...


3

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


2

I highly recommend this alternative approach to get an IMMUTABLE unaccent() function: CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text AS $func$ SELECT unaccent('unaccent', $1) $func$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp; Use that function for the expression index (and in all queries). Detailed explanation in this related ...


2

Try with this modification: Once with : WITH (INDEX (IX_SRCHGT)) -- the present case Once with WITH (INDEX (PK_SRCHGT)) DECLARE CUR_SRCHGT_UPDATE CURSOR FOR SELECT CRDATE FROM SRCHGT WITH (INDEX (IX_SRCHGT)) --SRCHGT WITH (INDEX (PK_SRCHGT)) WHERE or use keyset as type of the Cursor DECLARE CUR_SRCHGT_UPDATE CURSOR ...


2

Have you tested the lz6 compression level? lz6 provides less overall compression but it has a index on values which you can pull out without going through the overhead and cost of uncompressing the whole file. I If you cannot give up any compression at all, have you tried a simple json document store DB such as Mongo? I feel using filestream in SQL ...


2

Look at the estimated query plan, and make sure the index is being used. Also, the complexity of your MultiPolygon could be a significant factor. If you imagine your index as a series of grids over your MultiPolygon, there will be grids that either completely covered by your MultiPolygon or completely not covered by your MultiPolygon. Your ProblemChild ...


1

the following query will find all the queries that are using that particular index at realtime select i.object_id, i.index_id, i.name, tqp.query_plan,pl.plan_handle, pl.start_time,pl.estimated_completion_time,q.text from sys.indexes as i cross apply (select quotename(i.name) as name) as i2 cross apply sys.dm_exec_requests as pl ...


0

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%. Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases. While these are resource intensive database operations, I would prefer ...



Top 50 recent answers are included