Tag Info

Hot answers tagged

13

A few suggestions: Remove the DISTINCT as the ID is the primary key. There is no way you'll get duplicate rows in the result. Do not convert the datetime columns. This makes your conditions non-sargable and the query will always do a table scan. The variables would need no conversion either if they are declared as dates but that is not a problem for ...


3

@Lennart's answer is one way to negate your query, but it only selects países that are associated with other fabricantes_distribuidores. If you want to include países that are not linked with any distribuidor, too, you have to add an outer join (I use a left outer join as per convention): SELECT DISTINCT nom.pais_id FROM ...


3

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries In which case you can reformulate your query to look like: SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2; because you can find the lower bound for col2 from the lower bound for col1, like this: ...


2

@Joishi already provided an explanation for what you saw. Here is a solution to make it fast. Your query (unchanged) after trimming some noise: SELECT m.* FROM posts p JOIN messages m ON m.post_id = p.id WHERE p.project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972' AND m.kind = ANY ('{10,11,12}') ORDER BY m.updated_at DESC LIMIT 100 OFFSET 0; ...


2

I assume you mean not associated to a certain neg.fabricante_distribuidor_id and neg.producto_solicitud_id = 1. The easiest solution is to negate the predicate in the where clause: WHERE NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 ) You may prefer: WHERE neg.fabricante_distribuidor_id <> 1 OR ...


2

A filtered index (WHERE IsMostRecentRun = 1) sounds like a better idea to me than using sparse. If you can make it so that false is instead represented by null, you may be able to do both, but while that will potentially save some space in the base table, I suspect the bigger gain would be in query performance from the filtered index - as long as it's ...


2

No, but there is a workaround. :) I found parsel (parallel select) plpgsql function, which splits your query based on primary key, then connects to the database via dblink extension and waits for all subqueries. https://gist.github.com/mjgleaso/8031067 Author also wrote article about this function: ...


1

Explanation My question is: why does this not use the index amplifier_saturation_start? Even with 30,000,000 rows, only 3,500 in the date range it can be faster to read tuples from the top of the index amplifier_saturation_lddate on lddate. The first row that passes the filter on start can be returned as is. No sort step needed. With a perfectly ...


1

If you ONLY want to optimise for this query. This is the best index: ALTER TABLE items ADD INDEX (category, created_at, user_id) This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index. We can ...


1

Have you tried using ranges? Maythe the indexing methods for them (gist) can give you desired performance (for large datasets). Though gist indexes come with some tradeoffs (size, build-time, index-time for simple queries). Some testing code: create table t(id serial primary key, some_col text, foo numrange); insert into t(some_col, foo) select 'foobar', ...


1

TL;DR - Try removing the LIMIT and see which one performs better. Based off what you have pasted ... It appears that, because you are using a LIMIT on each .. the second query will run faster because it only has to apply one filter (project_id = '') while the second has to apply two filters (project_id = '' and kind = ''). As a result of using limit, it ...


1

I think you might be a bit confused about virtualization. Which virtualization platform are you using? I havent come across one yet that doesnt allow you to map the drives however you choose. There are default configs and settings that allow the hypervisor to decide how to best make use of the available host's resources, but I have never seen an offering ...


1

For bulk operations in index order a larger page size can give a noticeable, sometimes significant, performance increase. For very random access, particularly many small writes, there is similar potential for significant performance reduction. A single read of a 16K block will take four times longer than the read for a 4K block, assuming the drive heads are ...


1

I have now moved from dev to production. the table FactBackOrder has 4,183,289 rows. We're in sql server 2012. Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) But I don't want to use Columnstore indexes - these tables are ...



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