Tag Info

Hot answers tagged

5

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful. If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your ...


3

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify? Basic statistics in pg_class: SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE oid = 'schema_defs'::regclass; Data histograms per column in pg_stats (pg_statistics): SELECT attname, inherited, n_distinct, ...


3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry): ALTER TABLE entry_log ADD INDEX card_no__date__last_entry__ix (card_no, date, last_entry) ; and then use this query: SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG FROM entry_log WHERE card_no = LPAD('2948', 32, '0') ORDER ...


3

Cleanse your data before storing it. Otherwise, INDEXes may be useless. I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless. This SELECT would run a lot faster because of the index: SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = '2948' OK, you don't like the ...


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

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 ...


2

The datcollate column of pg_database stores LC_COLLATE for this database An other page of the documentation about collations says: The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of ...


2

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 ...


2

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

MyISAM tables with FULLTEXT index and other index(es). SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics AS s1 JOIN information_schema.statistics AS s2 USING (TABLE_SCHEMA, TABLE_NAME) JOIN information_schema.TABLES AS t USING (TABLE_SCHEMA, TABLE_NAME) WHERE s1.index_type = 'FULLTEXT' AND ...


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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible