Tag Info

Hot answers tagged

14

Is there any way to write a query that just seeks into that composite index key and then follows it along to retrieve the next chunk of 1000 rows? A favourite solution of mine is to use an API cursor: SET NOCOUNT ON; SET STATISTICS IO ON; DECLARE @cur integer, -- FAST_FORWARD, AUTO_FETCH, AUTO_CLOSE, CHECK_ACCEPTED_TYPES, ...


6

Yes. One potential benefit is that the slot array is ordered by index key order so matching row(s) on the page can be found by performing a binary search (or sometimes linear interpolation) rather than reading all rows on the page. SQL Server, Seeks, and Binary Search contains much more details about this.


4

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice: Perform a table / clustered index scan (therefore all columns are there) Perform a seek, then perform lookups to retrieve the columns not covered Which way it will choose depends on a variety of things, including how narrow the index is, how many rows ...


4

It sounds like this is more of a DataWarehouse workload than an OLTP. In that case, heavily indexed tables are normal. Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it). I know your using 2008R2 but ...


3

A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type. Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data. ...


2

Here is a suggestion for a rewrite. Perhaps your ORM can be convinced to produce this query. It's only slightly different from the original. The only change is a derived table instead of the base table star. As the tables have foreign keys defined and the query starting from star, follows the foreign keys, the result will be the same: SELECT * FROM ...


2

Please don't fooled by the error message The table 'my_table' is full. This rare scenario has absolutely nothing to do with diskspace. This table full condition has to do with the internal plumbing of InnoDB. First, take a look at this diagram of the InnoDB Architecture Please note that the system tablespace (the file ibdata) only has 128 Rollback ...


2

bytea will be optimal for storing the hash. It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them. For best results, store as bytea and have the client application use a PQexecParams call that requests binary results. Though on ...


2

In Postgres, a unique constraint is implemented with a unique B-tree index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. Indexes use the same basic storing mechanisms as tables: arrays of data pages. Indexes have some minor additional features. ...


2

You would create the index if any of this is true: There are queries or DML statements that benefit from this index. You want to enforce uniqueness of the combination of the index columns. You would not create the index if all of this is true: There are no queries or DML statements that benefit from it. You don't want to enforce uniqueness of these ...


2

In preference order: 4,1,2,3 Do not use UUIDs as the clustering key if using SQL server as, not only will it fragment badly, the clustering key is used in all non clustered indexes and you'd add those bytes to each index row. Fragmentation can be mitigated by using NEWSEQUENTIALID but usually prefer a bingint identity for your Clustering Key over a GUID to ...


2

Back in the day we would bulk load our data in this way: Drop indexes Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way) After the load is completed, create the clustered index Next, create any additional non-clustered indexes Miller time (this was before I could afford decent beer) That ...


1

If you are doing a large, load operation it is faster to utilize the TokuMX bulk loader, as it only requires one pass over the data to create both the primary key index and any secondary indexes. More information is available in the documentation at http://docs.tokutek.com/tokumx/tokumx-commands.html#tokumx-new-commands-loader


1

One would have to suppose that any natively-supported data type would be better optimised in the product than anything that could be put together as a client of that product. After that, whatever has the smallest byte count so you get the maximum rows per page.


1

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this: create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm')); However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is ...


1

I am assuming that you want the Oracle optimizer to pick tableb as the driver table and for each row in tableb, have it run an index scan for tablea (Since tablea has 3GB of data). Your indexes start with col4, so I am guessing you are hoping that one of them will be used by the optimizer. One thing to point out is that in your WHERE clause you have both: ...


1

Here are my notices: Make sure that creator exists only in table1, or add [... WHERE table1.creator=..] It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned ...


1

Matching index After re-reading your question I realized you are not running Amazon Redshift, but Amazon RDS, which seems to be running unsullied Postgres, at least according to the documentation: Amazon RDS supports DB instances running several versions of PostgreSQL. Currently we support PostgreSQL versions 9.3.1, 9.3.2, and 9.3.3. This would mean ...


1

From the documentation: Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. Full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls I suggest reading that entire page and then asking a more specific question if the optimizer is ...


1

This may be about the selectivity of your predicates. If a query uses an index it suffers the overhead of reading from disk the pages which constitute the index itself. If instead it performs a table scan it has the overhead of retrieving data it will ignore. The relative cost of these two options will depend on the selectivity of your index, how the ...


1

Take a look at http://ola.hallengren.com/ He has a great maintenanceplan which is free to use. I am using it for every project.


1

The reason that your table is growing while only making updates is that PostgreSQL is that "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done." Of course, while you are updating 1000 rows per second your updates are in contention with VACUUM FULL and perhaps REINDEX. ...


1

Is there any way to calculate indexes multithreaded for single inserts? Unfortunately, no. PostgreSQL backends are single-threaded, and a single connection can use only one backend. There's work to enhance this, but it's a seriously difficult and big job because of PostgreSQL's process-based architecture. The only real option is to split the insert ...


1

will the rows be indexed during "COPY" since the table has an implicit index? Yes. if the answer is yes, the "COPY" will slow down? Yes. You can LOCK TABLE ... IN ACCESS EXCLUSIVE MODE then drop the PRIMARY KEY constraint for the duration if you need to. PostgreSQL doesn't have a way to mark indexes as invalid then revalidate them for use ...


1

The good thing with unique indexes is that search stops when the first value matches, but that requires the WHERE part to match exactly with the index. In your case the index will be big. If you are lucky the value might be found quickly on the b-tree, else it might need to scan almost the entire index.



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