For a query like this, a partial index covering only unsynced rows would serve best. CREATE INDEX ON tbl (id) WHERE sync_done = FALSE; However, for a use case like this, other synchronization methods may be preferable to begin with: Have a look at LISTEN / NOTIFY. Or use a trigger in combination with dblink … Read more
Just a hunch, but normally a TOP-ORDER BY requires SQL Server to buffer the result in some way (either the index scan’s result or indeed the entire result in a temp structure, or anything in between). One could argue that for cursors this is also necessary even when ordering by the primary key (as in … Read more
sp_updatestats updates all statistics for all tables in the database, where even a single row has changed. It does it using the default sample, meaning it doesn’t scan all rows in the table so it will likely produce less accurate statistics than the alternatives. If you have a maintenance plan with ‘rebuild indexes’ included, it … Read more
I’ve tried it out on MySQL 8.0.13 and compared LIKE vs REGEXP on a table with 1M+ rows on a column with an index: SELECT * FROM table WHERE column LIKE ‘%foobar%’; Query took 10.0418 seconds. SELECT * FROM table WHERE REGEXP_LIKE(column, ‘foobar’); Query took 11.0742 seconds. LIKE performance is faster. If you can get … Read more
Why do spring/hibernate read-only database transactions run slower than read-write? <tldr> The short answer to question #1 was that hibernate starts off a @Transaction(readOnly = true) session with a set session.transaction.read.only synchronous JDBC call and ends with a set session.transaction.read.write call. These calls are not sent when doing read-write calls which is why read-only calls … Read more
It depends on what kind of application you are building. Create a representative test scenario, and start hammering away. Then you will know the definitive answer. Besides your use case, it also depends on CPU, memory, front-side bus, operating system, cache settings, etcetera. Seriously, just test your own scenario. If you need some numbers (that … Read more
Use uuid. PostgreSQL has the native type for a reason. It stores the uuid internally as a 128-bit binary field. Your other proposed options store it as hexadecimal, which is very inefficient in comparison. Not only that, but: uuid does a simple bytewise sort for ordering. text, char and varchar consider collations and locales, which … Read more
ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn’t remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with … Read more
You don’t seem interested in the actual count so give this a try: SELECT 1 FROM table WHERE fk = 1 LIMIT 5000, 1 If a row is returned, you have 5000 and more records. I presume the fk column is indexed.
You will definitely start to run into issues on that 42 GB table once it no longer fits in memory. In fact, as soon as it does not fit in memory anymore, performance will degrade extremely quickly. One way to test is to put that table on another machine with less RAM and see how … Read more