This will help to aggregate the information -

Connection Count by ClientIP, with Total

For Mongo Shell:

db.currentOp(true).inprog.reduce((accumulator, connection) => { ipaddress = connection.client ? connection.client.split(":")[0] : "unknown"; accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1; accumulator["TOTAL_CONNECTION_COUNT"]++; return accumulator; }, { TOTAL_CONNECTION_COUNT: 0 })

Formatted:

db.currentOp(true).inprog.reduce(
  (accumulator, connection) => {
    ipaddress = connection.client ? connection.client.split(":")[0] : "unknown";
    accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1;
    accumulator["TOTAL_CONNECTION_COUNT"]++;
    return accumulator;
  },
  { TOTAL_CONNECTION_COUNT: 0 }
)

Example Output:

{
    "TOTAL_CONNECTION_COUNT" : 331,
    "192.168.253.72" : 8,
    "192.168.254.42" : 17,
    "127.0.0.1" : 3,
    "192.168.248.66" : 2,
    "11.178.12.244" : 2,
    "unknown" : 41,
    "3.100.12.33" : 86,
    "11.148.23.34" : 168,
    "81.127.34.11" : 1,
    "84.147.25.17" : 3
}

(the 192.x.x.x addresses are internal Atlas monitoring)


If it does not take any parameters in or out then

BEGIN
DISABLE_TRIGGERS;
END;

Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;

I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences.  This code as originally posted should be more comprehensive if used in production, particularly for a migration.  Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.

Does the migration depend on any business logic in the triggers?
Will users be able to access the database during the migration? Hope not, any actions they take will not use the trigger logic.
does the success or failure of the migration depend on triggers being disabled?

You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.

A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers

something like

CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
v_action VARCHAR2(200);
BEGIN
ASSERT (action_in IN (0,1));  --where assert is part of your own error package
IF action_in = 0
THEN
  v_action := 'DISABLE';
ELSIF action_in = 1
THEN
  v_action := 'ENABLE';
END IF;
  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP);
--ELSE
--log result to a table
--END IF;

END CHANGE_TRIGGERS;


Connection to Oracle DB 19c fails when listener is set to the host IP address

Maybe add something like this to your listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = POLA)
      (ORACLE_HOME= C:\Polarbear\WINDOWS.X64_193000_db_home)
      (SID_NAME = POLA)
    )
  )

You might run this query to verify your global name:
select global_name from global_name;

SID should be
select INSTANCE_NAME from v$instance;

Bobby


Which are the most often used SQL queries?

It depends...

This is extremely specific to the environment.  There are two types:

OLAP (Online Analytical Processing) - this type of system is designed more for reporting and analysis, in which case often times SELECTs dominate the majority of the queries.

OLTP (Online Transaction Processing) - this type of system is designed more for data manipulation, and typically INSERT`UPDATE\DELETEstatements are significant enough to optimize for this type of transaction.SELECT` is also a large part of this environment, and considerations need to be made for this as well.

Often times these roles are encompassed by a single instance/database.  Because of this both must be considered.  There are dominant systems (like Data Warehouses) that may be dedicated soley to OLAP.

  I just want to know if joins outweigh selects in the long run

Joins simply create a relationship between tables.  They are used in conjuction with the respective DML statement (SELECT, INSERT, UPDATE, DELETE).

  or inserts outweigh creates

It sounds like you're asking the ratio of DDL vs. DML.  In a typical implementation DML will be the majority of a production environment's queries, though DDL is almost never going to be stale.


Is SQL Server Browser Service compulsory for remote access?

The SQL Server Browser Service does many different things... However the main purpose of it is to look up the port number associated with a given instance of SQL Server and giving that information back to the client.

Any instance of SQL Server (assuming tcp/ip) can be identified by using the computer name or ip address and the port number. If you know both of these things (or SQL Server is listening on the default port of 1433) then you'll be able to connect without using the browser service as an intermediary.

To do so, you can connect using the name or IP and the port:

MyComputer,1500
10.1.150.8,1600 (generic randomly picked ip for display purposes only)
MyComputer (this will assume default port of 1433)

If you are not using browser, I would advise you to disable it.


How to model a database structure for exam dates and date ranges?

I think I would approach this as the exam-dates table wouldn't have an entry for category 4, since it is an open-ended exam.

Perhaps, if you need to know when student A took an exam under category 4, then add a column for the date taken along with the student and student's grade?

So you could have

an Exam table that lists out the exams and other data like course id, etc.
an ExamDates table that lists when exams are scheduled (and possibly other  values like building, room number, etc.)
a Students table with all of your student data.
a StudentExams table that links exams to students, (and through exams back to courses). This would have a student ID, an Exam ID, a date the test was completed, and a grade.

and probably some other tables like

a Courses table with data about the course
a StudentCourses table linking student to course
and so on...


How to Get All Server Side Traces in SqlServer from SSMS, stop it from SSMS and delete the log files

Trace properties with NULL values indicate options were not specified when the trace was created. Properties are listed in the fn_trace_getinfo documentation and can be included a query for easy reference:

SELECT traceid, trace.property, trace_properties.description, trace.value
FROM fn_trace_getinfo(DEFAULT) AS trace
JOIN (VALUES
     (1, 'Trace options. For more information, see @options in sp_trace_create (Transact-SQL)')
    ,(2, 'File name')
    ,(3, 'Max size')
    ,(4, 'Stop time')
    ,(5, 'Current trace status. 0 = stopped. 1 = running.')
    ) AS trace_properties(property, description) ON trace_properties.property = trace.property
ORDER BY trace.traceid, trace.property;

Rowset traces (Profiler or SMO) will not have values pertaining to server-side traces, such as file name, max size, etc. Below are example results of the above query including the default trace and a running Profiler trace.

+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
| traceid | property |                                     description                                     |                                      value                                      |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
|       1 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 2                                                                               |
|       1 |        2 | File name                                                                           | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc |
|       1 |        3 | Max size                                                                            | 20                                                                              |
|       1 |        4 | Stop time                                                                           | NULL                                                                            |
|       1 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
|       2 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 1                                                                               |
|       2 |        2 | File name                                                                           | NULL                                                                            |
|       2 |        3 | Max size                                                                            | NULL                                                                            |
|       2 |        4 | Stop time                                                                           | NULL                                                                            |
|       2 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+

The trace properties are also exposed as normalized columns in sys.traces. This DMV includes all legacy SQL Traces (but not Extended Event traces) regardless of whether its a server-side trace to a file or rowset trace returning data to a Profiler client. You may find it easier to query sys.traces instead. Example output from SELECT * FROM sys.traces.

+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
| id | status |                                      path                                       | max_size | stop_time | max_files | is_rowset | is_rollover | is_shutdown | is_default | buffer_count | buffer_size | file_position | reader_spid |       start_time        |     last_event_time     | event_count | dropped_event_count |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
|  1 |      1 | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc | 20       | NULL      | 5         |         0 |           1 |           0 |          1 |            2 |        1024 | 1048576       | NULL        | 2020-05-20 07:44:55.487 | 2020-05-24 08:25:08.983 |        1088 | NULL                |
|  2 |      1 | NULL                                                                            | NULL     | NULL      | NULL      |         1 |           0 |           0 |          0 |         1088 |           1 | NULL          | 94          | 2020-05-24 08:18:00.503 | 2020-05-24 08:25:08.983 |          74 | 0                   |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+

EDIT:

A trace can be stopped and deleted in SSMS by executing sp_trace_setstatus from a query window, specifying the desired trace id and status (0 = stop, 2 = delete). For example, the rowset trace with traceid 2 above can be stopped and deleted with:

EXEC sp_trace_setstatus 2, 0; --stop trace id 2
EXEC sp_trace_setstatus 2, 2; --delete trace id 2


Microsoft Certification - 70-764 Administering a SQL Database Infrastructure

Q1: Previously, I have purchased other Microsoft Certification books and have normally got a PDF version, along with the printed book. Is this not the case anymore? Am I missing a disk or something?

On the page you linked to, there were 3 options: book, ebook, and book + ebook. You would need to buy the book + ebook option if you wanted both.

Q2: The book has many URLS for supporting resources, however, these don't appear to be valid any longer. For example, early on within the book, there is a URL given for a list of useful links which accompany the book, I cannot find these online at all.

Yeah, typical problem with dead tree printed versions of the internet: the links are often out of date. You can try using the Wayback Machine at archive.org to see if they've been archived.

Q3: Practice tests also used to come with the book, however this does not appear to be the case any longer. Is this by design? Or am I just missing something here?

On the page you linked to, it says, "This title is also available for sale with a practice test." You would want to click that link and buy the other version with the practice test if that's important to you.


Given a table containing class and students, need to find the diff in students

Use, for example

SELECT student_id
FROM class_students
GROUP BY student_id
HAVING SUM(class_id = 1) > 0
   AND SUM(class_id != 1) = 0

More informative is

SELECT student_id, GROUP_CONCAT(DISTINCT class_id) classes_in
FROM class_students
GROUP BY student_id
HAVING COUNT(DISTINCT class_id) < ( SELECT COUNT(DISTINCT class_id)
                                    FROM class_students )

PS. Recommendation - define (class_id, student_id) (or in backward fields order) as unique/primary index - it will forbid duplicated records.


How to migrate from SQL Server 2000 to SQL Server 2008 R2 Express Edition

Whichever method you use, you'll need to be aware of any restrictions on SQL Server 2008 R2 Express that might be a show-stopper eg 10GB maximum size of any one database, single CPU, memory restrictions etc

Ideally, you'll also have tested how the application works with the new version of SQL Server. If it's a vendor product, do they support the new SQL version?

Backup and restore is the most robust method of moving a database as it leaves the original database online (if the SQL Server is left online), and it's part of what DBAs do every day. You'll need to set up the users on the new server etc.... and if it doesn't work, your original is still online. Backup and restore can be done through the GUI Management Studio.

Detach and re-attach is another option where you separate the data & log files from the original SQL Server, and physically copy them to the new SQL Server - and then attach them. It can be done through the GUI. This method is probably a bit less refined than backup and restore - just my opinion.

Whichever method you use, run DBCC CHECKDB on the new server to ensure all is well, and don't forget to schedule regular backups, checkdb, and reindexing etc.


How to automate CSV-exports of queries in PL/SQL Developer (Oracle)?

One option (on MS Windows) is to

create SQL file which spools data into a file
create BAT script which calls that SQL script
schedule a daily job in Task Scheduler

Prerequisite is to install SQLPlus, if you don't have it already. PL/SQL Developer is a GUI tool; SQLPlus is a command-line tool. It can be installed via Oracle Client software, downloadable from Oracle Technology Network's Download section.

SQL file, based on Scott's schema, might look like this:

set linesize 100
set pagesize 100
set colsep ';'

spool dept.txt
select * from dept;
spool off

spool emp.txt
select * from emp;
spool off;

exit

BAT file:

sqlplus -s -l scott/tiger@xe @daily.sql

Task Scheduler: create a job which will run the BAT script at desired time; make it run in a directory you have access to (such as C:\Temp or similar).

Resulting DEPT.TXT file looks like this:

    DEPTNO;DNAME         ;LOC
----------;--------------;-------------
        10;ACCOUNTING    ;NEW YORK
        20;RESEARCH      ;DALLAS
        30;SALES         ;CHICAGO
        40;OPERATIONS    ;BOSTON

List of SET commands in the SQL file is really simple - there are numerous options you might want to research. For example, this is a longer list with more options (including column formatting):

set termout off
set trimspool on
set echo off
set verify off
set autoprint off
set serveroutput off
set arraysize 1000
set pagesize 0
set linesize 100
set long 10000
set numwidth 10
set feedback off
set colsep ';'
col empno format 99999
col ename format a10
col sal format 999G990

You'll have to try these (and, possibly some more) to find a set which suits your needs.


How to generate and insert primary key when using SELECT DISTINCT in OracleDB?

Let the database generate it for you (identity column, 12c feature):

CREATE TABLE users(
   id NUMBER generated always as identity, -- <= HERE
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
);

If you are still on a version below 12c, then you can simply use rownum:

INSERT INTO testUser(id, username, email)
SELECT rownum, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a sequence:

create sequence s1;
INSERT INTO testUser(id, username, email)
SELECT s1.nextval, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a trigger, as described here: https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle


Displaying a value associated with a maximum value

You could use a window function here, but I would think there's actually a better solution with DISTINCT ON.
First I simplified what you had so far:
SELECT p.name AS product_name, p.description AS product_description
     , a.stock_sum, b.purchase_sum
     , c.max_sales, o.outlet_name   -- still missing
FROM   Product p
LEFT   JOIN (
   SELECT product_id, SUM(copies) AS stock_sum
   FROM   Stock
   GROUP  BY 1
   ) a USING (product_id)
LEFT   JOIN (
   SELECT product_id, sum(copies) AS purchase_sum
   FROM   PurchaseItem
   GROUP  BY 1
   ) b USING (product_id)
-- c, o still  missing

It should be considerably faster to aggregate counts before joining:

Why does the following join increase the query time significantly?

Also, LEFT JOIN retains products in the result that don't have any purchases, yet, or aren't in stock any more.
Then add the missing parts:
LEFT   JOIN (
   SELECT DISTINCT ON (product_id)
          pi.product_id, pu.outlet_id, sum(copies) AS max_sales
   FROM   Purchase      pu
   JOIN   PurchaseItem  pi USING (purchase_id)
   GROUP  BY 1, 2
   ORDER  BY 1, sum(copies) DESC NULLS LAST
   ) c  USING (product_id)
LEFT   JOIN Outlet o USING (outlet_id);

About DISTINCT ON:

Select first row in each GROUP BY group?

You can run DISTINCT over the results of the aggregation. Consider the sequence of events in a query:

Best way to get result count before LIMIT was applied in PHP/PostgreSQL

Optimize performance
It's probably cheaper to scan PurchaseItem only once, using a CTE. But this also adds some overhead. You'll have to test which is faster:
WITH ct AS (
   SELECT pi.product_id, pu.outlet_id, sum(pi.copies) AS sales
   FROM   PurchaseItem  pi
   JOIN   Purchase      pu USING (purchase_id)
   GROUP  BY 1, 2
   )
SELECT p.name AS product_name, p.description AS product_description
     , a.stock_sum, b.purchase_sum
     , c.max_sales, o.outlet_name
FROM   Product p
LEFT   JOIN (
   SELECT product_id, SUM(copies) AS stock_sum
   FROM   Stock
   GROUP  BY 1
   ) a USING (product_id)
LEFT   JOIN (
   SELECT product_id, sum(sales) AS purchase_sum
   FROM   ct
   GROUP  BY 1
   ) b USING (product_id)
LEFT   JOIN (
   SELECT DISTINCT ON (product_id)
          product_id, outlet_id, sales AS max_sales
   FROM   ct
   ORDER  BY product_id, sales DESC
   ) c  USING (product_id)
LEFT   JOIN Outlet o USING (outlet_id);

Test performance with EXPLAIN ANALYZE (a couple of times to exclude caching effects).


Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?

Very few SQL language features are different between editions.

Some language constructs (CREATE PARTITION for example and related) are additional (as opposed to different) and won't work because these are Enterprise features.

However, for day to day code, nothing special, things are almost identical.

The only thing I can recall that is different is how indexed views are used


Update Schema in SSIS Export Package When Source Schema Changes

Easy? Not really - SSIS is very picky about the metadata that is used in the data flow, so changing schema is challenging to deal with.

My typical solution for these types of scenarios is to generate the packages through either the API, or using Biml (BI Markup Language). {Disclaimer: My employer (Varigence) created Biml.}

The API is a fair amount of work, and assumes you are comfortable with .NET development. Biml is an alternative that doesn't require as much .NET knowledge. You can use Biml through BIDS Helper, which is a free download.


Apparently the problem was the > operator. Changing that to -f to specify the file for the dump, resolved the problem (using the plain format).

I was writing a Powershell script which may have been the underlying cause. The commands were accepted, but there could have been a corruption issue there.


Complicated SSRS layout - is that possible?

If you're using a single dataset which returns a single row, this should be pretty easy to achieve.

Not with subreports, but rather "sub tables". Create separate tables for certain areas, then drag and drop them in to appropriately sized cells within the main table.

Create a table, then another with the content you want to place in a certain position. Size the container cell accordingly (you can merge horizontal cells in the master to achieve this if necessary) or else things start to go a bit weird:

Then drag & drop the sub-table in to place:

The tables have to have a dataset in common, which is fine in this context if I understand your description. And I think there are limits as to which area of a table you can place sub-tables, but other than that it can be a pretty powerful tool.

And I think your approach of ignoring headers and footers is right for this example. Just use detail rows for everything. Create as many as you need.

Hopefully I've given you enough to experiment with. I can expand on this at a later date if you need it.


It is impossible to run this command:

GRANT EXECUTE ON `mysql`.`store_time_zone` TO 'user'@'%';

Here is why:

The EXECUTE grant exists at the global level
The EXECUTE grant exists at the database level
The EXECUTE grant does not exist at the table level
The EXECUTE grant does not exist at the column level

Here is how you can prove it:

User grants for MySQL exist in four(4) MyISAM tables

mysql.user (Global grants)
mysql.db (Database level grants)
mysql.tables_priv (Table level grants)
mysql.columns_priv (Column level grants)

If you run this query

mysql> select concat(table_schema,'.',table_name)
    -> from information_schema.columns
    -> where table_schema='mysql'
    -> and column_name='execute_priv';
+-------------------------------------+
| concat(table_schema,'.',table_name) |
+-------------------------------------+
| mysql.db                            |
| mysql.user                          |
+-------------------------------------+
2 rows in set (0.03 sec)

mysql>

Only mysql.user and mysql.db have columns in the mysql schema representing the EXECUTE privilege.

Therefore, these commands would work

# Global level EXECUTE privilege
GRANT EXECUTE ON *.* TO 'user'@'%';
# Database level EXECUTE privilege
GRANT EXECUTE ON `mysql`.* TO 'user'@'%';


Why does FROM multiple tables default to cartesian product?

The syntax of comma-style joins (SQL-89) versus using the JOIN keyword (SQL-92) is not the point. You should use the more modern syntax, but it doesn't address the question of why the default would be a Cartesian product if you do not specify a condition.
The answer to that is that it makes relational algebra work.
A Cartesian product is the set of all combinations of elements of two sets. Each element of the first set is paired with every element of the second set.
A relation is a subset of a Cartesian product. It's a Cartesian product, plus a condition which tests if any given pair of elements belongs to the relation.
But the default condition is a fixed true value, so every pairing passes the test. So the default relation ends up being the Cartesian product.
The alternative, I suppose would have been to make the default condition a fixed false value, so the default relation would be an empty relation.
That would have made some scenarios easier, for example if you run a DELETE but accidentally forget the WHERE clause, it would save you from deleting your whole table.
But then we would be getting questions on Stack Overflow from different people who ask why the default join is an empty set, because that makes their heart skip a beat when they forget their WHERE clause for a SELECT query and it appears that their database is empty.


Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or contained database user) before an application role can be activated.

Application roles mostly differ from regular database roles in that they:

have no members
are enabled using sp_setapprole with the app role password after successful authentication
are unset using sp_unsetapprole with the cookie returned by sp_setapprole (required for connection pooling)

There is no value in using an application role when a service account (with the permissions needed by the application) is used for application database access.

A use case for application roles is when users connect to SQL Server from the application using their own security credentials (e.g. Windows authentication via Windows group membership) but do not have the permissions the application requires. Activating an application role in this scenario allows auditing of individual user activity (e.g. using ORIGINAL_LOGIN()) without granting permissions beyond CONNECT, although users could be granted permissions for needs outside the application, such as those needed for a reporting tool. This would allow read-only access for reporting while limiting writes via the application.


SQL Server transaction log - shrinking and reattaching

No. After you restored the database in your development environment it had become a different database. Re-attaching it back to production will replace the production database and all transaction that occurred after your initial detach will be lost. Doing what you describe usually require very complex solutions, involving setting up replication from production to staging and contiguously replicating transactions in production to the staging server. Needless to say, developing/testing such a complex solution is only worth it for a ... worthy goal. Shrinking your database is not such a worthy goal.

Handling situation of runaway data that was never designed for delete (which is 99% of all projects, since the need for efficient delete of data is never evident during development, when the tables are empty) is quite hard actually. Partitioning is the best solution, by far, but is a huge undertaking with serious impact on the application. Other than that, deleting old data in small batches is the next best thing, provided an appropriate supporting index is provisioned.


select query on postgis not working for specific fields

Probably this columns names has been created using double-quotes.

You should use double-quotes in your SELECT statement.

create table tbl (field1 int, "FIELD2" int, field3 int);

insert into tbl values (1,2,3),(4,5,6);

select field1, field2, field3 from tbl;

ERROR:  column "field2" does not exist
LINE 1: select field1, field2, field3 from tbl;
                       ^
HINT:  Perhaps you meant to reference the column "tbl.field1" or the column "tbl.field3".

select field1, "FIELD2", field3 from tbl;

field1 | FIELD2 | field3
-----: | -----: | -----:
     1 |      2 |      3
     4 |      5 |      6

db<>fiddle here


How to find n consecutive numbers in MySql

Substitute $n with the desired number:

SELECT c1.id
FROM consecutive c1
WHERE $n = (SELECT count(*)
           FROM consecutive c2
           WHERE c2.id
           BETWEEN c1.id AND (c1.id + $n - 1)
           );

This does a full table scan for each number, combined with a range scan. It finds all possible combinations (returning the first figure in the chain, so add a LIMIT 1 for better performance if you just want to find the first result. Example:

mysql> CREATE TABLE consecutive (id int PRIMARY KEY);
mysql> INSERT INTO consecutive VALUES (1),(2),(3),(4),(6),(7),(8),(9),(10),(11);
mysql> SELECT c1.id FROM consecutive c1 WHERE 5 =
       (SELECT count(*) FROM consecutive c2
        WHERE c2.id BETWEEN c1.id AND (c1.id + 4)
       ) LIMIT 1;
+----+
| id |
+----+
|  6 |
+----+
1 row in set (0.00 sec)

It could probably be optimized to be done in a simple table scan with the help of variables, but this uses simpler and more standard syntax.


I guess you can use CHECK constraint

ALTER TABLE foo
  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;


For your request:

  sorted by alphabets and then by numeric values

I assume (deriving from your sample data) you want to ORDER BY:

The first letter, treated as text.
The first number (consecutive digits), treated as integer.
The whole string to break remaining ties, treated as text. May or may not be needed.

SELECT *
FROM   tbl
ORDER  BY left(col, 1)  -- 1st letter as text
     , substring(fest, '\d+')::int NULLS FIRST  -- first number in string as int
     , col  -- whole columns as cheap tiebreaker

SQL Fiddle.

More details:

Alphanumeric sorting with PostgreSQL
Humanized or natural number sorting of mixed word-and-number strings


Backup compression in SQL server 2008R2 and above

You can't completely predict it, but you can backup smaller databases with compression and if the data sets are similar (based on the factors listed below), you can predict a ratio that you may be able to apply to the larger databases.

It's easiest to just quote the MSDN on compression here:

To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size

For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained. Major factors include:

The type of data.
Character data compresses more than other types of data.
The consistency of the data among rows on a page.
Typically, if a page contains several rows in which a field contains
the same value, significant compression might occur for that value.
In contrast, for a database that contains random data or that
contains only one large row per page, a compressed backup would be
almost as large as an uncompressed backup.
Whether the data is encrypted.
Encrypted data compresses significantly less than equivalent
unencrypted data. If transparent data encryption is used to encrypt
an entire database, compressing backups might not reduce their size
by much, if at all.
Whether the database is compressed.
If the database is compressed, compressing backups might not reduce
their size by much, if at all.


Azure SQL database query performance degradation

After discussion, the issue here is that the optimizer in SQL Server/SQL Azure currently "sniffs" current parameter values when compiling the query.  The query plan is then cached and used on future executions of the query.  The plan that is generated may be more optimal for the originally sniffed value, but if other values are more commonly used at execution time (or, more broadly, if the impact on the rest of the query is different statistically such as one value being uncommon when sniffed vs. more common cases being sniffed), then this can be a source of runtime variance in a data application.  There are a few knobs to control this at different scopes, but using the option OPTIMIZE FOR UNKNOWN query hint will cause the optimizer to not sniff.  This will generate a query plan that is more generic (but still usually reasonably fast).
The original question suggested that clearing out the query store "fixed" the problem, but this was not actually the real cause.  It so happens that clearing the query store will force recompiles for all the query plans it was tracking on the next execution of each query.  That just allowed the optimizer to sniff a different value and, at least for the customer's experience, it happened to be better in that experiment.  Anything that forces a recompile would lead to a similar result.  You could do DBCC FREEPROCCACHE (empties the plan cache), reboot your server, force/unforce the plan for the query in query store, any metadata change to the database or tables involved in the query, etc.


Database Design for Paper Submission System

If all you want to do is keep track of which authors contributed to which papers, then all you need is a simple intersecting entity (see more) like this:

Here the columns of the SUBMISSION table are just the two primary key columns from PAPER and AUTHOR.  These two columns will jointly form the primary key of the intersecting table and will also each individually be foreign keys to their respective tables.

NOTE: Don't add any other columns to the SUBMISSION table, unless those columns can reasonably be expected to vary row by row for the same paper and for the same author.  For example, it would be a bad idea to add a date to the submission table unless a single paper is submitted at various times by multiple authors.  If you need to track information about the event of an author or group of authors submitting a paper, then you need to normalize out the extra information to avoid creating update anomalies.  Consider the following:

In the above ERD, notice how the extra information that is not author-dependent remains at the SUBMISSION level, while the new intersecting entity CONTRIBUTION is added to record the connections between authors and a particular instance of the submission of a paper.

This four table model is more robust because it allows for papers to be resubmitted multiple times with changes in authorship each time.  If this kind of real world nuance is important to your system then you would be better off with the four table approach than with a three table approach.

Here is the four table model with more column details at OP's request:


UPDATE pallet
SET    pallet_status = 2
WHERE  pallet.pallet_code in (SELECT Pallet_code
                              FROM (
                                   SELECT p.Pallet_code,
                                          COUNT(ps.SOHP_ID) AS "Roll Count"
                                   FROM   PALLET p, PSOH ps
                                   WHERE  p.pallet_code = ps.SOHP_bin
                                   AND    p.Plant_Code = 36
                                   AND    p.Pallet_Status = 1
                                   AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
                                   GROUP BY   p.pallet_code
                                   HAVING     COUNT(ps.sohp_id) = 0
                                   ));


Can I restore an uncompressed differential backup after a compressed full backup?

This is really a question for the DBA site but yes, it will work. Compression is transparent when restoring, and the RESTORE command doesn't need any parameter to tell it that a backup is compressed.

Of course you can easily test this yourself.


I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

A hard drive's IOPS tells you how many I/O operations that particular drive can perform per second.  Since random reads, random writes, sequential reads, and sequential writes have different performance characteristics, if you're looking at a single number, that implies that this is a weighted average of these four numbers that represents some workload.  That workload may or may not be similar to the workload that a database would actually perform so your system may end up getting more or fewer operations per second.

A SQL query would, assuming it does physical I/O (many queries do not need to do any I/O because they are simply reading from data already in the database's memory or in the file system cache or in the SAN cache), tend to perform many I/O operations.  If your queries are actually doing physical I/O, a single query could easily do 100 I/O operations per second.  You could potentially have 100 queries running simultaneously each only getting to do 1 physical I/O operation per second but that would likely mean that all 100 queries would take an unacceptable amount of time to return.

Realistically, if you need to handle 3000 queries per second, you'll need to ensure that the vast majority of your queries do not need to do physical I/O by ensuring that the data they need is cached (preferrably by the database).  And you'll need to spread the I/O over a relatively large number of drives (with an appropriate RAID configuration) to increase the total IOPS that the system can perform.  This sort of capacity planning is going to require a reasonably good understanding of your application, your data, how much I/O each query is likely to really need, etc.  And you'll likely need to balance cost vs. performance-- adding memory or adding drives or changing the RAID configuration will involve different tradeoffs between cost, performance, and available space that will have to be considered.  You may also want to consider splitting the workload among multiple servers (which may require architectural changes to your application).


SQL Server - Database per company. How to query across databases?

I don't know if this will help, and this maybe a little much for your situation, but here goes our solution in use today.

All users are grouped into logical entities under a domain (something.com) umbrella. Our particular scenario required an additional layer of Domain->Company->Group->User break out. Not sure if you ever worked with Active Directory or domain trees, but it follows that logic.

Using the domain model, the server itself is at the top, presumably the client of your services would be the administrator. Each company flows like a branch from the server itself. Then flows into user-defined groups which contain users. Each company in this scenario would have a Domain Admin which can administer the xyz.com, abc.com, etc domains, but can't access any other domain.

Each of the service containers (the databases) use a full trust model with the security database to provide services to the users contained in this security database (Think OpenID for perspective). We use a home brewed C++ compiled module for Apache 2.x to provide an application firewall and session security host.

Each run, the module "asks" the security database to record the page hit, produce a random (64 character string) cookie and a session cookie (two total cookies), and authenticate the session. A new session or the same session is returned based on if the cookies match. The session key and anything else relevant is provided to the end-user UI application code over HTTP headers (so the firewalls could sit in front of Google Apps or AppEngine).

Once the UI has the code, the UI can act on behalf of the user and the service database accepts this token and provides direct permissions to the user based on the token. Our application provides the username as well to match a unique user within the service database to provide extended permissions. Since we also use per-transaction random keys, the state is unable to be cached.

This model supports isolation (UI developers are unable to eavesdrop), allows a central security with a delegation aspect, and the ability to provide centralized services. Such as providing a central forum, trending (statistical compiled) data stored centrally (which maybe restricted to paying clients), or maybe even weblogs as we provide to each of our service databases to make decisions to gauge a fraudulent checkout request.

    SERVERS TABLE Minimum of id (unique key), domain, adminuser
                |
                | -------> OPTIONAL Access Control List
                |
    SERVER USER CONTROL LIST Minimum of id (unique key), serverid, actual url you wish to protect.
                OPTIONAL restrictions on AdminOnly, NoSearchEngines, Restricted (Authentication Required)
                |
                |
                | --------> OPTIONAL ENTITY (Sub-Company) Abstraction
                |
                | --------> OPTIONAL GROUPS
                |
                |
    USERS   Minimum of id (unique key), username, serverid (unless you use the entity container which are already tied to the server)
                |
                |
                |
    PERMISSIONS Minimum of id (unique key), server_ucl_id, groupid/entityid/userid (Depending on preference)

    SESSIONS    Minimum of id (unique key), serverid, userid if authenticated

    Provide the sessionid and either userid or username to tamper-resistant proxy code that will provide it to the end-user UI.

We have a lot of bells and whistles in our app to provide a more robust multi-tenant solution, but this is the basics of what we are doing.

The objective is to totally isolate each company into it's own container. Proxy code of some sort should help. C++ is not required, just need code to sit in front of the web application. Also if you want to provide security in one database and shared services in another database, your shared database, which presumably will also be under your client's control, would be allowed to query directly to the security database to see if the user is logged in based on the tokens provided to it, such as when the user posts.

There are no error messages to humans using this model. If anything, any errors would either be pushed via some type of messaging que, email, or log files. The authentication layer is between the proxy code and the security database. User management is within an application that you create for the end-user companies.

This model also scales well with SQL Azure in the middle if needed as it does not need anything like CLR, FT, or etc. The only true weak point will always be the proxy code for security enthusiast. Physical security, and limited user access should lock that down.

Hope I wasn't to verbose and that this helps!


Geography data type and Upgrading from 2008 r2 to SQLServer 2014

Upgrade should not fail (For this reason anyway).  As stated in the link you provided, this is an application issue and only if it is directly accessing the Microsoft.SqlServer.Types.dll.

The upgrade from 2008r2 to 2014 should work fine.


there are no queries in the slow log

Then, make sure they will be. One of the easiest ways to profile MySQL queries is through the slow log. if you find no slow queries is because you probably have a lot of them that take lower that 10 seconds to execute, or that are locked waiting (that time is not considered, "execution time"). Set long_query_time to a lower value, and if you want to profile fully the server, you can temporarily set it to 0 (Warning: may have a bin impact on your query performance), to log all your queries.

That, combined with aggregation tools like pt-query-digest is the 101 of server query optimization. There are other monitoring tools and techniques, but this is a good place to start. You may not identify 100% of the problems, but 90% in my experience.

  I feel pretty confident that MySQL is reaching a performance maximum.

That is an assumption without data supporting it. Get evidence first (a.k.a. profile), then evaluate options.

  each time [...]CPU % on mysql goes up by about 100%

That is not necessarily a bad thing. Resources are being used. The question is if they are doing it efficiently (that is the real question).


Saving images as base64 encoded strings, why is it bad?

The one reason I can see is that you are doing unnecessary work that way. PostgreSQL will compress and toast the base64 string, so you have to pay the price of compression and decompression, unless you set the column to EXTERNAL, then you don't compress, but you waste storage space and I/O bandwidth.

Hint: if you store compressed binary data in PostgreSQL, set the bytea column to EXTERNAL storage. Otherwise you waste CPU in a futile attempt to compress the data.


ActiveX script throwing runtime error inside DTS package

Although I am NOT proficient with .net code, this particular example seems like a good fit to use .net or SSIS components listed below. You could even use PS along with SSIS.

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm

http://pragmaticworks.com/help/dtsxchange/scr/FAQ%20-%20How%20to%20convert%20Scripting.FileSystem%20object%20of%20ActiveX%20Script%20to%20native%20SSIS%20Task.htm

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm


The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally.

Modifications are incremental (deltas) as I explain in detail in Indexed View Maintenance in Execution Plans (a full recount is not performed on every base table update); however, you do need to ensure that the delta update parts of the execution plan have efficient access methods (like any query).

It is typically quite simple to identify a missing index from the INSERT/UPDATE/DELETE execution plan. Perhaps you could add an illustrative post-execution (actual) execution plan to your question.

Automatic matching of query text to an indexed view is only available in Enterprise Edition (and equivalents). In other editions, you must use the WITH (NOEXPAND) table hint. There are also good reasons to use NOEXPAND even on Enterprise Edition.

Regarding the demo code: Make sure you specify the hint using WITH (NOEXPAND). The way you have written it, NOEXPAND is parsed as an alias. Note also that only materialized (indexed) views can have a NOEXPAND hint.

If you are unable to add a hint directly, this would be an excellent use of a Plan Guide. A plan guide can also be used to ensure that a query that matches an indexed view (without naming it explicitly) actually uses the indexed view.

Remember that without NOEXPAND on a materialized (indexed) view, SQL Server always expands the view definition at the start of plan compilation. Enterprise Edition may (or may not) match (parts of) a query to an indexed view depending on its assessment of the costs of each option.

Related Q & A:

How can I make sure the SQL Server query optimizer uses the exact tables in the query
Why does the READPAST hint cause indexed views to be ignored?
What factors go into an Indexed View's Clustered Index being selected?


You can't have a PRIMARY KEY on a VIEW. See here, here . You have to put PRIMARY KEYs and INDEXes on the underlying tables. A VIEW is a stored query - you can't have KEYs on a query.


Using DATE part of datetime column in WHERE clause

Community wiki answer:

Try an inclusive start date and exclusive end date:

date_time >= @start AND date_time < DATEADD(day, 1, @end)

See also What do BETWEEN and the devil have in common? by Aaron Bertrand.

Sargability wouldn't be a problem if the query is written to use CAST(date_time AS DATE), since SQL Server will include a helper covering seek predicate. See Understanding SARGability (to make your queries run faster) by Rob Farley (recorded at SQLBits VII in 2010) which demoed this. Note however that the conversion might still affect the accuracy of cardinality estimates.


Automated SP_WhoIsActive SQL Activity Capturing Issue

It appears from the Primary Key name that you are running Adam Machanic's sp_WhoIsActive and storing the result in a table.
If that is the case, the warnings are normal and caused by the options you set when calling sp_WhoIsActive. The Primary Key violation is what is causing the job to fail.  Take a look at the definition of the primary key.
I believe you will find that the Primay Key needs to be changed in order to insure uniqueness.
In my implementations for this I leave the table as a heap and do not define a primary key on this table. I only keep a few days of data in the table and do not use it for replication.
Here is an article on how to implement it:
Brent Ozar Implementation of sp_WhoIsActive


Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.


How to load 100k + Records into progres database from a CSV file?

You can use the COPY command for this.

See:
http://www.postgresql.org/docs/9.3/static/sql-copy.html
https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table


Why does PK constraint require a separate index when I already have a suitable unique clustered index?

It doesn't need a separate index. It's only done that way because you've actually told it to be in the script. You could do this with the same functionality:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [JobItemsIndex] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);


Yes, using ENABLE_BROKER in the RESTORE conflicts with NORECOVERY. If your database is in NORECOVERY you'll need to wait until it is recovered before you renable the broker. If you are having trouble, try renabling with rollback immediate:

ALTER DATABASE db_name SET ENABLE_BROKER with rollback immediate


MySQL find_in_set function not work as expect

FIND_IN_SET ( GROUP_CONCAT ) -- cleaver, but clunky.  Instead of
select *
from report_user_foundation
where !FIND_IN_SET(user_id ,(
    select GROUP_CONCAT(login_user_ids) as bet_user_ids
    from report_summary rs
    where statistic_time >= 1606752000000)
)

do
SELECT *
    FROM report_user_foundation AS rsf
    WHERE NOT EXISTS(
            SELECT 1 FROM report_summary
                   ON rsf.user_id = login_user_ids
                  AND statistic_time >= 1606752000000
                    )
   /* AND login_user_ids like '%4685%' */

or, alternatively,
SELECT rsf.*
    FROM report_user_foundation AS rsf
    LEFT JOIN report_summary rs
                   ON rsf.user_id = rs.login_user_ids
                  AND rs.statistic_time >= 1606752000000
    WHERE rs.id IS NULL
   /* AND login_user_ids like '%4685%' */

Alas, I don't understand your comment about where login_user_ids like '%4685%'.  Perhaps it goes where I put the comments.
If these tables get big, this composite index may help:
report_summary:  INDEX(login_user_ids, statistic_time)


If I use varaibles in the order by clause, is there any guarantee the result will be sorted?

OK,lets try to write a query that:

doesn't use variables in any way:
is correct and produces the wanted output always:

SELECT q.*, 0 as aa_id
FROM table1 AS q
WHERE q.type = 0

UNION ALL

SELECT ans.*, q.acceptedanswerid as aa_id
FROM table1 AS q
  JOIN table1 AS ans
    ON ans.related = q.id
WHERE q.type = 0
  AND ans.type = 1

ORDER BY
    type,
    CASE WHEN id = aa_id THEN 0 ELSE 1 END,
    timestamp ;

or simplified with a LEFT JOIN:

    SELECT a.*
    FROM table1 AS a
      LEFT JOIN table1 AS q
        ON  q.id = a.related
        AND q.type = 0
    ORDER BY
        type,
        CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
        timestamp ;

The real issue is the design of the table. The answers should be linked to their related question but they aren't. If there is some way to identify which is answer is related to which question, then the query shoudl be modified to take that into account. Until then, the query (and the table!) is only useful to store one question only and its answers.

As for the question about variables, no you shouldn't use them that way. you may get expected results but it's not safe and may break, either in a future release or due to some optimizer settings change or even because some index was added and a different plan was chosen.

You probably want this order in the results:

ORDER BY
    related,
    type,
    CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
    timestamp ;


The problem I see with what you propose is that a user who has the ability to grant privileges to other users... is, almost by definition, not a "limited" user.

You can't grant privileges at all without the GRANT privilege, and even with that, you can't grant a specific privilege that you don't possess ... unless you have permission to manipulate the grant tables directly, in which case, you're not exactly a limited user either.

But, aha, here's your workaround.

Stored procedures run with the credentials of the user who defined them or as the explicitly-specified definer.  (You have to have SUPER to specify somebody else as DEFINER.)  Any user with the EXECUTE privilege on a stored procedure can execute the procedure, and the procedure essentially escalates their privilege level while it is running.

If you wrap your administrative operations in (well-written) procedures, then you don't have to actually give the limited user permission to do anything, other than run the procedures.

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`change_password` $$
-- root@localhost is an example; use an appropriate local user that has the
-- permissions that need to be available for the operation to succeed
CREATE DEFINER='root'@'localhost' PROCEDURE `mysql`.`change_password` (
  IN dirty_user VARCHAR(16),
  IN dirty_host VARCHAR(40),
  IN dirty_password VARCHAR(41))
BEGIN

  DECLARE encrypted_password TINYTEXT DEFAULT PASSWORD(dirty_password);
  DECLARE clean_user TINYTEXT DEFAULT NULL;
  DECLARE clean_host TINYTEXT DEFAULT NULL;

  SELECT user, host
    FROM mysql.user
   WHERE user = dirty_user
     AND host = dirty_host
    INTO clean_user, clean_host;

  IF clean_user IS NULL OR clean_host IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user/host provided does not exist';
  END IF;

  SET @_sql = CONCAT_WS('\'','SET PASSWORD FOR ',clean_user,'@',clean_host,' = ',encrypted_password,'');

  PREPARE stmt FROM @_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @_sql = NULL;

END $$

DELIMITER ;

GRANT EXECUTE ON PROCEDURE mysql.change_password TO 'limited'@'%';

The 'limited'@'%' user can now change passwords for other users even though they don't have permission to do it themselves, by using CALL mysql.change_password('user','host','password');.

This user does not have permissions themselves, but does have execute on the proc; they can't do it directly:

mysql> set password for 'wombat'@'localhost' = PASSWORD('secret');
ERROR 1044 (42000): Access denied for user 'limited'@'%' to database 'mysql'

... but they can do it this way.  Note that "0 rows affected" is not a meaningful value.

mysql> call mysql.change_password('wombat','localhost','secret');

Query OK, 0 rows affected (0.00 sec)

Notes:

String-concatenated queries with input parameters are unacceptable, but the SET PASSWORD statement does not accept ? positional-parameters, so there's no choice here.  To sanitize the inputs, I take care of the password by encrypting it in advance, outside the prepared statement, and concatenated the encrypted password there; I've sanitized the user and host by actually selecting them from the mysql.user table and using the values I've selected, also outside the prepared statement.  If they aren't found, we can't set their password, so we throw an exception using SIGNAL.  If they are found, we concatenate the fetched values into the prepared statement, sanitizing them by this mechanism.  It's still theoretically possible that bad data in the mysql.user table could render the quoting of the prepared statement invalid, but if you have bad data in the mysql.user table, then you have 2 problems (1 problem in addition to this one).
Any explicit GRANT EXECUTE you've given at the procedure level disappears from the mysql.procs_priv table if you drop and redefine the procedure, so if you make changes to the procedure, you have to re-grant the privileges to the limited user.
You need MySQL 5.5+ for the SIGNAL statement to be valid.  Below 5.5 you can replace it with a hack, something like CALL mysql.`change_password(): the user/host provided`; (note the backticks) which will throw a not quite as pretty, but still serviceable message complaining that the bogus procedure name quoted in the backticks... does not exist.  Heh... ERROR 1305 (42000): PROCEDURE mysql.change_password(): the user/host provided does not exist


Why can't a null be equal to a null for the sake of a join?

Just tell Oracle to do that:

select *
from one t1
  join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1);

(Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that)

But this will only work if the replacement value (-1 in the example above) does not actually appear in the table. Finding such a "magic" value for numbers might be possible, but it will be very difficult for character values (especially because Oracle treats an empty string as null as well)

Plus: no index on the id columns will be used (you could define a function based index with the coalesce() expression though).

Another option that works for all types, without magic values:

              on t1.id = t2.id or (t1.id is null and t2.id is null)

But the real question is: does this make sense?

Consider the following sample data:

Table one

id
----
1
2
(null)
(null)

Table two

id
----
1
2
(null)
(null)
(null)

Which of combination of null values should be chosen in the join? My above example will result in something like a cross join for all null values.

T1_ID  | T2_ID
-------+-------
     1 |      1
     2 |      2
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)


SQL Server - Uniqueness Of The Transaction ID

So, how can I get such a unique id for a transaction in the transaction logs?

Stop trying to use the transaction log for these types of situations. Take any notion you have of using the log for data mining and defenestrate it.

  Or should I use SQL server audit? Is there a way to query the audit id from inside the transaction?

Judging by your other questions, I don't think SQL Audit would work for you. Most likely you're going to need to change your application and build in auditing to gather what you're looking for due to the complex requirements you are looking for.

The application could be changed to generate its' own transaction id with the id of the web user, etc., and log that to an auditing area. That's, however, up to the application but would get you want you want. Attempting to continue to mine transaction log data will at best cause you throughput issues and at worse crash/suspect your database.


The performance is going to be atrocious with your setup, especially in writes because every write has to be acknowledged by at least one remote node. You will also get an astronomical number of deadlocks.
If you need geo-replicated synchronous redundancy, you would be batter off with with DRBD for block level replication. Performance will still be terrible on commit latency but not as terrible as with Galera.
You need to rethink your requirements and your approach to fulfilling them.


To do this, I did the following:

CREATED the two TABLEs

CREATE TABLE table1
(
  t1_field1 VARCHAR (20),
  t1_field2 VARCHAR (20)
);

CREATE TABLE table2
(
  t2_field1 VARCHAR (20),
  t2_field2 VARCHAR (20)
);

And populated it with data:

INSERT INTO table1 VALUES ('123', 'test1');
INSERT INTO table1 VALUES ('234', 'test2');

INSERT INTO table2 VALUES ('12345', 'test2');
INSERT INTO table2 VALUES ('23245', 'test3');
INSERT INTO table2 VALUES ('123945', 'test6');

And then ran the following SQL:

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2
FROM table1 t1, table2 t2
WHERE CAST(t1.t1_field1 AS VARCHAR(3)) = CAST(t2.t2_field1 AS VARCHAR(3));

Giving the result:

 t1_field1 | t1_field2 | t2_field1 | t2_field2
-----------+-----------+-----------+-----------
       123 | test1     |     12345 | test2
       123 | test1     |    123945 | test6
(2 rows)

This SQL works if t1_field1 and t2_field1 are both VARCHARs or INTEGERs (and if one is VARCHAR and one is INTEGER).

This SQL works if they are both VARCHARs -

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2
FROM table1 t1, table2 t2
WHERE LEFT(t1.t1_field1, 3) = LEFT(t2.t2_field1, 3);

I don't have S4 HANA to hand, but this is standard SQL and according to the documentation I Googled, it should work for Sybase which might be related?


mySQL data table seems to stuck at 16777215 rows

The maximum value for your auto_increment 'seq_number' field is 16777215.  You should make this field use an int or a bigint if you need more that 16,777,215 records.

See the MySQL Documentation for info about the mediumint(9) format.

https://dev.mysql.com/doc/refman/5.0/en/integer-types.html


May be the following links may help you:

https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing
https://www.brentozar.com/archive/2015/04/microsoft-sql-server-licensing-simplified-into-7-rules/
https://www.brentozar.com/archive/2014/09/first-concern-sql-virtualization-licensing/


You need to use a mysql user that has permissions from the host your executing the script from.

Run select user, host from mysql.user on the database to see if your user has an entry for the IP you're connecting from. If not, you will have to grant permissions to the user for that host.


How do I query for leaf nodes in a hierarchy using a closure table?

I think this is what you want (edited):

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me
    leaf.*                                 -- all
FROM
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            --
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            --
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

Test at SQL-Fiddle (where 'FT' is used instead of 'trumpet', to match your data.)


Mysql Incremental Backup and point in time recovery

You can use  https://sourceforge.net/projects/mysqlincrementalbackup/ script. Description from its website:

  A complete incremental backup for MyISAM and InnodB in a mix
  environment for those applications use both of engines simultaneously
  using binary logs and a method that does not affect running database.
  There is no need to stop or lock the database, It does utilize only
  binary logs to extract update queries of databases. This tools uses
  automysqlbackup script as part of solution for its full backup.


Is high index fragmentation an issue on performance

Will this cause any performance issues?

I would start by quoting from Old Books Online for SQL Server 2000 version. Microsoft Books Online (The SQL Server 2000 BOL is now removed by MS so the link is not present. Although its for SQL Server 2000 but gives very accurate definition )

  Fragmentation affects disk I/O. Therefore, focus on the larger indexes
  because their pages are less likely to be cached by SQL Server. Use
  the page count reported by DBCC SHOWCONTIG to get an idea of the size
  of the indexes (each page is 8 KB in size). Generally, you should not
  be concerned with fragmentation levels of indexes with less than 1,000
  pages. In the tests, indexes containing more than 10,000 pages
  realized performance gains, with the biggest gains on indexes with
  significantly more pages (greater than 50,000 pages).

Plus and fact being that since index is small pages are most likely to be in memory so no physical I/O would be required to get page and hence  fragmentation would not come in picture.

So the small indexes which have page_count <1500 or for that sake < 3000 is hardly going to cause any performance issue, it is quite likely that pages would be in memory and physical I/O would not be required hence fragmentation would actually not matter. If I look at your output only two indexes with page_count 66K+ should really cause some performance issue if SQL Server has to scan index pages. People can argue that page_count of 5000 could also be deciding factor and to which I would say "not really", considering the definition from old books online quoted above. You must also note that the fragmentation we are talking is logical fragmentation where leaf level page order does not matches that of clustered index key.

So performance issue, if any, could only come from large fragmented index not the smaller ones.


UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I do not believe there is any issue here with regards to the User-Defined Type (UDT) as I am able to execute this successfully:

Please note that I am using the Free version of the SQL# library (which I wrote) to test with:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [SQL#].[Type_HashTable] NULL
);

CREATE STATISTICS [weirdStatistics] ON [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params]);

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN;

No errors. However, if I remove the stats object name (the error did mention a missing object name) but keep the square brackets, then I am able to reproduce the error:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [] WITH FULLSCAN;

returns:

  Msg 1038, Level 15, State 4, Line 28
  An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

And no square brackets at all works just fine (since it updates all stats on the table):

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] WITH FULLSCAN;

So it really needs to be empty square brackets. My guess is that your weekly Update Stats job (I am assuming it is a SQL Agent Job since you said "weekly") is somehow leaving out the stats object name.

AND, since the question actually mentions auto-created statistics, that should be tested as well, just to be sure that there isn't an issue with those (there shouldn't be, but it's still best to test and know for sure):

-- get rid of user-created statistics object:
DROP STATISTICS [dbo].[Dic:Table:With&Weird:Name].[weirdStatistics];

-- add one row so that we can query it:
INSERT INTO dbo.[Dic:Table:With&Weird:Name]
VALUES (1, 2, N'ddfg', 'df:uiui', 'dfgdfgd', 0, 44, 'type', N'key1=SomeVal');

-- get SQL Server to auto-create the statistics object on this column:
SELECT *
FROM   dbo.[Dic:Table:With&Weird:Name]
WHERE  [GUI:Edit:Params] IS NOT NULL;

-- get the dynamically generated statistics object name and use it for UPDATE STATS:
DECLARE @StatName sysname;

SELECT @StatName = st.[name]
FROM sys.stats st
INNER JOIN sys.stats_columns sc
        ON sc.[object_id] = st.[object_id]
       AND sc.[stats_id] = st.[stats_id]
INNER JOIN sys.columns col
        ON col.[object_id] = sc.[object_id]
       AND col.[column_id] = sc.[column_id]
WHERE st.[object_id] = OBJECT_ID(N'dbo.Dic:Table:With&Weird:Name')
AND   col.[name] = N'GUI:Edit:Params';

SELECT @StatName AS [StatisticsName];

EXEC (N'UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [' + @StatName
      + N'] WITH FULLSCAN;');

The code above also completes without errors :-).

The following test should be done to separate the UDT from this particular usage of it (i.e. this table). The example below again uses SQL#, but you should replace [SQL#].[Type_HashTable] with [dbo].[TParams] and then replace the string value in the first INSERT statement with the appropriately formatted initialization text. If this does not error then the issue is with that table, possibly with the column name itself, such as having a hidden character in it. If it does error then we need to look at the definition of that UDT.

CREATE TABLE [dbo].[tmpTestStats](
    [Id] [tinyint] NOT NULL,
    [UDT] [SQL#].[Type_HashTable] NULL
);

INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (1, N'k1=v1&k2=v2');
INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (2, NULL);

CREATE STATISTICS [tmpTestStats_UDT] ON [dbo].[tmpTestStats]([UDT]);

UPDATE STATISTICS [dbo].[tmpTestStats] [tmpTestStats_UDT] WITH FULLSCAN;


MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

I saw an interesting answer to a question about the biggest blob you may have

Here is the statement I saw in ServerFault

innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

I did not see any InnoDB Settings in your my.cnf

I see you disabled the InnoDB Built-in and you plugged in another InnoDB

See if you can adjust innodb_log_file_size and innodb_log_buffer_size to accommodate.

Since you only have 4GB of RAM reserved, try making your InnoDB Log Files Much Bigger

Change innodb_log_file_size to 1G in /etc/my.cnf

service mysql stop
sleep 30
rm -f /var/lib/mysql/in_logfile[01]
service mysql start (recreates ib_logfile0 and ib_logfile1)

Give it a Try !!!


What are the drawbacks with using UUID or GUID as a primary key?

It depends on your generation function and size of the final tables

GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates.

From the scope of your problem, and the need for offline writes, you've quite neatly boxed out the use of anything but a GUID, and therefore there are no compensatory advantages of other schemes.

From a functional standpoint, the key length is usually not an issue on any kind of modern system, depending on the number of reads and size of the table. As an alternative methodology, offline clients could batch new records without a primary key and simply insert them when reconnecting. As postgreSQL offers the "Serial" datatype, clients will never need to determine the ID if they can perform a simple write to the database.


Getting “SQL Server Alert System: 'Alert Severity 20' occurred” error occasionally

Check the SQL Server error log and you will find a corresponding log message that includes the IP address of the originating system.  This will be the most important clue to investigating what is causing this.

It is most likely that this is being caused by security software that is scanning/probing your server for vulnerabilities.  If you find that is not the case, then it is either something that is accidentally or maliciously attempting to make a connection to whatever port SQL Server is listening on.


Posgres Row Security Policy - Why Array faster than Bitstring?

It doesn't matter very much how big the data structure is, as long as the row is small enough not to get TOASTed (stored out of line, in which case access becomes more expensive).

The performance difference you measure might as well be the cost of the different operators (& vs. array element access).

I don't believe that you can do that more efficiently than you did. Row level security comes with a performance hit that is ideally proportional to the number of rows checked, and the performance impact will be most visible with a cheap access path like a sequential scan.


Oracle installation requires old rpm versions, what to do?


use CTE & ROW_NUMBER() function

declare @T TABLE
(name nvarchar(55),surname nvarchar(55), dates datetime, value decimal(18,2))

insert into @T values ('David','Example','2015-08-24 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.8)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)

;with cte as
(SELECT name,surname, [Dates],[Value],ROW_NUMBER() OVER (PARTITION BY cast(dates as date) ORDER BY value desc)  AS RN
FROM @t)

select name,surname, [Dates],[Value] from cte
where RN = 1


How to match regular expressions in Oracle?

You'd need to use regexp_like not like if you want to use regular expressions.  It sounds like you want

with x as (
    select 'A1B C2D' postal_code from dual union all
    select 'XXX 777' from dual union all
    select 'a1B 2C3' from dual
 )
 select *
   from x
  where regexp_like( postal_code, '^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$', 'i' );


MySQL 5.5.58 Installing Multiple Instances on RHEL7

why dnt you start new instance with different cnf file (keeping diffrent PORT and diffrent socket file)

for example :

cd /opt/mysql_installation/u01/mysql_5.7.17/bin/
./bin/mysqld_safe --defaults-file=/opt/mysql_installation/my.cnf &


SQL Server 2012 Can't log in with machine account

It is most likely not the DOMAIN\computer$ at all. This message is known to be a misleading. I think all it wants is that  "NT AUTHORITY\NETWORK SERVICE" be granted the server access. Open SQL Server login properties and add this account. Also, check what user is used in the IIS to impersonate anonymous user, it should be something like "IUSR_ServerName". Grant access to this user as well


Yes, you can run those queries in pgbench. Look for the parts talking about custom scripts:

http://www.postgresql.org/docs/9.4/static/pgbench.html

The max_connections, and shared_buffers seem really high for the server you have. 25% of RAM for shared_buffers would be more reasonable, and getting a connection pooler like pgbouncer to handle lots of connections would be much more productive.

You might find this useful as well:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Hope that helps. =)


Prod environment uses a different SSIS version to Dev when running a legacy SSIS package

The solution ended up being nothing to do with running different versions of the SSIS Windows service, but how SSIS 14.0 in SQL Server 2017 handles legacy SSIS packages which use configuration files. I use configuration files to store database credentials.

In SQL Server 2008 R2 when you deploy a SSIS package, it also asks you where you want to store the configuration file and the deployed package references that configuration file in the file location you specify.

In SQL Server 2017 when you deploy a legacy SSIS package, it grabs the contents of the configuration file and stores it within the deployed project. The configuration parameters are then configurable via the SSMS SSIS GUI.

Here's the gotcha, in SQL Server 2017, if the configuration file exists in the original file location when the SSIS deployment package was built, it uses the contents of that configuration file in preference over any configuration defined in the SSIS package node of SQL Server 2017. If the configuration file is removed/renamed from the original file location (when the SSIS deployment package was built), it then falls back to using the configuration defined via the GUI in the SSIS package node of SQL Server 2017.

This doesn't make a whole heap of sense, but this is definitely what is happening.

Hopefully this solution might help someone somewhere!


Get partial match from GIN indexed TSVECTOR column

Do all queries have to be be in dictionary?

No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly:
No. Because, on top of that Full Text Search is also capable of prefix matching:
This would work:
SELECT id, subject
FROM   mailboxes
WHERE  tsv @@ to_tsquery('simple', 'avail:*')
ORDER  BY id DESC;
Note 3 things:

Use to_tsquery(), not plainto_tsquery(), in this case because (quoting the manual):

... plainto_tsquery will not recognize tsquery operators, weight labels, or prefix-match labels in its input

Use the 'simple' text search configuration to generate the tsquery since you obviously want to take the word 'avail' as is and not apply stemming.

Append :* to make it a prefix search, i.e find all lexemes starting with 'avail'.

Important: This is a prefix search on lexemes (word stems) in the document. A regular expression match without wildcards (content ~* 'avail') is not exactly the same! The latter is not left-anchored (to the start of lexemes) and would also find 'FOOavail' etc.
It's unclear whether you want the behavior outlined in your query or the equivalent of the added regular expression. Trigram indexes (pg_trgm) like @Evan already suggested are the right tool for that. There are many related questions on dba.SE, try a search.
Overview:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Demo
SELECT *
FROM (
   VALUES
     ('Zend has no framework')
   , ('Zend Framework')
   ) sub(t), to_tsvector(t) AS tsv
WHERE tsv @@ to_tsquery('zend <-> fram:*');

 id |       t        |          tsv
----+----------------+------------------------
  2 | Zend Framework | 'framework':2 'zend':1

Related answer (see chapter "Different approach to optimize search"):

How can I generate all trailing substrings following a delimeter?

Emails?
Since you mentioned emails, be aware that the text search parser identifies emails and does not split them into separate words / lexemes. Consider:
SELECT ts_debug('english', 'xangr@some.domain.com')

(email,"Email address",xangr@some.domain.com,{simple},simple,{xangr@some.domain.com})

I would replace the separators @ and . in your emails with space (' ') to index contained words.
Also, since you are dealing with names in emails, not with English (or some other language) words, I would use the 'simple' text search configuration to disable stemming and other language features:
Build the ts_vector column with:
SELECT to_tsvector('simple', translate('joe.xangr@some.domain.com', '@.', '  ')) AS tsv;


All instances upgrade from Command Prompt

You can first download the SQL server 20XX edition of your needs here.
Then you can download and extract the local content media on your machine.
After that you should see in the downloaded folder the SETUP.EXE file
Then you can open the command prompt and type (By considering your sql standard instance name is MSSQLSERVER):
C:\Path_to_file\SETUP.EXE /QS /IACCEPTSQLSERVERLICENSETERMS /ACTION=UPGRADE /INSTANCENAME=MSSQLSERVER

When you connect to your database, you should see the new version installed
. In my case I upgraded from SQL server 2017 to SQL server 2019. Old Maj version was 14.XX, now the new Maj version is 15.XX. See below.

And there are plenty of cool explanations on this page
EDIT: I think its not possible to upgrade all instances at one with the allinstances parameter, because not allowed (Tried it).
Here is what the command line says:
 ALLINSTANCES                 Specifies that all instances are to be included
                              in the Setup operation. This parameter is
                              supported only when applying a patch.

Bu you can loop with powershell on all your instances with the above command. If you dont have 1 million instances, it should be fine.


Distributing MySQL database on n systems

This technique is called Sharding.

MySQL doesn't support this kind of partitioning therefore is necessary to implement sharding logic manually.

I think Spock Proxy, for example, it's a good starting point to think in this direction

Hope this helps


How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

Getting a relative path in SSMS is not that straight-forward since you are not executing the script; SSMS has loaded the script into memory and is executing its text. So the current directory/folder is the default process starting folder. You can see this by running the following in SQLCMD mode in SSMS:

!! PWD

However, I did find a kinda-sorta way to do this. I admit that this is not the super-most ideal way to do this, however, it currently seems to be the only way to get a true relative path (given that setting the path in a variable isn't really "relative" per se).

So what you can do is:

Execute a DOS command to find foo.sql and store the path to that file in a text file in a folder that you can get from SSMS, either because it is a hard-coded path or because it uses an environment variable that is available to both the DOS command and to SQLCMD mode in SSMS
When storing the path in that file, store it as a SQLCMD mode command that sets a variable to that path
Import that text file into SSMS using :r and it will set that variable to the desired path

!! CD C:\ & FOR /F %B IN ('DIR /B /A -HS /S foo.sql') DO ECHO :setvar mypath "%~dpB" > %TEMP%\relative_path.txt

:r $(TEMP)\relative_path.txt

:r $(mypath)\bar.sql

Notes:

The above method assumes only 1 file on the system is named foo.sql. If more than one file has that name, the last one found will be the path set in the relative_path.txt file
Doing the CD C:\ will start at the root of the C: drive. This is probably not the most efficient place to start. If you generally have your SQL files in an area such as C:\Users{YourLogin}\Documents\Visual Studio 2013\Projects, then just change the CD command to get closer to the destination, such as:

!! CD C:\Users\{YourLogin}\Documents\Visual Studio 2013 & FOR ...


Can't "SET TRUSTWORTHY ON" With Non-SA User Account

Oops, Look like I got one of my X's mixed up in the database name, apparently if you run:

ALTER DATABASE [XXX_DATABASEDOESNOTEXIST] SET TRUSTWORTHY ON;

You'll get this error... :(


T-SQL: How to avoid losing digits when divided by a number

Your problem is that you try to solve something mathematically, not by accounting standards.

For the LAST payment, do not use your formula. Instead take the full price and substract all partial prices.

So, A3 would be 100 - A1 - A2 and that would then come to a perfect number.

So, either the first or the last payment are calculated as "the rest missing from the others" instead of "one equal part".


How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I've done a lot of experimenting and here are my findings.

GIN and sorting

GIN index currently (as of version 9.4) can not assist ordering.

  Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order.

work_mem

Thanks Chris for pointing out to this configuration parameter.
It defaults to 4MB, and in case your recordset is larger, increasing work_mem to proper value (can be found from EXPLAIN ANALYSE) can significantly speed up sort operations.

ALTER SYSTEM SET work_mem TO '32MB';

Restart the server for change to take effect, then double check:

SHOW work_mem;

Original query

I've populated my database with 650k products with some categories holding up to 40k products. I've simplified query a bit by removing published clause:

SELECT * FROM products WHERE category_ids @> ARRAY [248688]
ORDER BY score DESC, title LIMIT 10 OFFSET 30000;

Limit  (cost=2435.62..2435.62 rows=1 width=1390) (actual time=1141.254..1141.256 rows=10 loops=1)
  ->  Sort  (cost=2434.00..2435.62 rows=646 width=1390) (actual time=1115.706..1140.513 rows=30010 loops=1)
        Sort Key: score, title
        Sort Method: external merge  Disk: 29656kB
        ->  Bitmap Heap Scan on products  (cost=17.01..2403.85 rows=646 width=1390) (actual time=11.831..25.646 rows=41666 loops=1)
              Recheck Cond: (category_ids @> '{248688}'::integer[])
              Heap Blocks: exact=6471
              ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=10.140..10.140 rows=41666 loops=1)
                    Index Cond: (category_ids @> '{248688}'::integer[])
Planning time: 0.288 ms
Execution time: 1146.322 ms

As we can see work_mem was not enough so we had Sort Method: external merge  Disk: 29656kB (the number here is approximate, it needs slightly more than 32MB for in-memory quicksort).

Reduce memory footprint

Don't select full records for sorting, use ids, apply sort, offset and limit, then load just 10 records we need:

SELECT * FROM products WHERE id in (
  SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 30000
) ORDER BY score DESC, title;

Sort  (cost=2444.10..2444.11 rows=1 width=1390) (actual time=707.861..707.862 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2436.05..2444.09 rows=1 width=1390) (actual time=707.764..707.803 rows=10 loops=1)
        ->  HashAggregate  (cost=2435.63..2435.64 rows=1 width=4) (actual time=707.744..707.746 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2435.62..2435.62 rows=1 width=72) (actual time=707.732..707.734 rows=10 loops=1)
                    ->  Sort  (cost=2434.00..2435.62 rows=646 width=72) (actual time=704.163..706.955 rows=30010 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: quicksort  Memory: 7396kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=11.587..35.076 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.883..9.883 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 0.682 ms
Execution time: 707.973 ms

Note Sort Method: quicksort  Memory: 7396kB. Result is much better.

JOIN and additional B-tree index

As Chris advised I've created additional index:

CREATE INDEX idx_test7 ON products (score DESC, title);

First I tried joining like this:

SELECT * FROM products NATURAL JOIN
  (SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 30000) c
ORDER BY score DESC, title;

Query plan differs slightly but result is the same:

Sort  (cost=2444.10..2444.11 rows=1 width=1390) (actual time=700.747..700.747 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2436.05..2444.09 rows=1 width=1390) (actual time=700.651..700.690 rows=10 loops=1)
        ->  HashAggregate  (cost=2435.63..2435.64 rows=1 width=4) (actual time=700.630..700.630 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2435.62..2435.62 rows=1 width=72) (actual time=700.619..700.619 rows=10 loops=1)
                    ->  Sort  (cost=2434.00..2435.62 rows=646 width=72) (actual time=697.304..699.868 rows=30010 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: quicksort  Memory: 7396kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=10.796..32.258 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.234..9.234 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 1.015 ms
Execution time: 700.918 ms

Playing with various offsets and product counts I could not make PostgreSQL use additional B-tree index.

So I went classical way and created junction table:

CREATE TABLE prodcats AS SELECT id AS product_id, unnest(category_ids) AS category_id FROM products;
CREATE INDEX idx_prodcats_cat_prod_id ON prodcats (category_id, product_id);

SELECT p.* FROM products p JOIN prodcats c ON (p.id=c.product_id)
WHERE c.category_id=248688
ORDER BY p.score DESC, p.title LIMIT 10 OFFSET 30000;

Limit  (cost=122480.06..122480.09 rows=10 width=1390) (actual time=1290.360..1290.362 rows=10 loops=1)
  ->  Sort  (cost=122405.06..122509.00 rows=41574 width=1390) (actual time=1264.250..1289.575 rows=30010 loops=1)
        Sort Key: p.score, p.title
        Sort Method: external merge  Disk: 29656kB
        ->  Merge Join  (cost=50.46..94061.13 rows=41574 width=1390) (actual time=117.746..182.048 rows=41666 loops=1)
              Merge Cond: (p.id = c.product_id)
              ->  Index Scan using products_pkey on products p  (cost=0.42..90738.43 rows=646067 width=1390) (actual time=0.034..116.313 rows=210283 loops=1)
              ->  Index Only Scan using idx_prodcats_cat_prod_id on prodcats c  (cost=0.43..1187.98 rows=41574 width=4) (actual time=0.022..7.137 rows=41666 loops=1)
                    Index Cond: (category_id = 248688)
                    Heap Fetches: 0
Planning time: 0.873 ms
Execution time: 1294.826 ms

Still not using B-tree index, resultset did not fit work_mem, hence poor results.

But under some circumstances, having large number of products and small offset PostgreSQL now decides to use B-tree index:

SELECT p.* FROM products p JOIN prodcats c ON (p.id=c.product_id)
WHERE c.category_id=248688
ORDER BY p.score DESC, p.title LIMIT 10 OFFSET 300;

Limit  (cost=3986.65..4119.51 rows=10 width=1390) (actual time=264.176..264.574 rows=10 loops=1)
  ->  Nested Loop  (cost=0.98..552334.77 rows=41574 width=1390) (actual time=250.378..264.558 rows=310 loops=1)
        ->  Index Scan using idx_test7 on products p  (cost=0.55..194665.62 rows=646067 width=1390) (actual time=0.030..83.026 rows=108037 loops=1)
        ->  Index Only Scan using idx_prodcats_cat_prod_id on prodcats c  (cost=0.43..0.54 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=108037)
              Index Cond: ((category_id = 248688) AND (product_id = p.id))
              Heap Fetches: 0
Planning time: 0.585 ms
Execution time: 264.664 ms

This is in fact quite logical as B-tree index here does not produce direct result, it is only used as a guide for sequential scan.

Let's compare with GIN query:

SELECT * FROM products WHERE id in (
  SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 300
) ORDER BY score DESC, title;

Sort  (cost=2519.53..2519.55 rows=10 width=1390) (actual time=143.809..143.809 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2435.14..2519.36 rows=10 width=1390) (actual time=143.693..143.736 rows=10 loops=1)
        ->  HashAggregate  (cost=2434.71..2434.81 rows=10 width=4) (actual time=143.678..143.680 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2434.56..2434.59 rows=10 width=72) (actual time=143.668..143.670 rows=10 loops=1)
                    ->  Sort  (cost=2433.81..2435.43 rows=646 width=72) (actual time=143.642..143.653 rows=310 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: top-N heapsort  Memory: 68kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=11.625..31.868 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.916..9.916 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 0.630 ms
Execution time: 143.921 ms

GIN's result is much better. I checked with various combinations of number of products and offset, under no circumstances junction table approach was any better.

The power of real index

In order for PostgreSQL to fully utilize index for sorting, all query WHERE parameters
as well as ORDER BY parameters must reside in single B-tree index.
To do this I have copied sort fields from product to junction table:

CREATE TABLE prodcats AS SELECT id AS product_id, unnest(category_ids) AS category_id, score, title FROM products;
CREATE INDEX idx_prodcats_1 ON prodcats (category_id, score DESC, title, product_id);

SELECT * FROM products WHERE id in (SELECT product_id FROM prodcats WHERE category_id=248688 ORDER BY score DESC, title LIMIT 10 OFFSET 30000) ORDER BY score DESC, title;

Sort  (cost=2149.65..2149.67 rows=10 width=1390) (actual time=7.011..7.011 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2065.26..2149.48 rows=10 width=1390) (actual time=6.916..6.950 rows=10 loops=1)
        ->  HashAggregate  (cost=2064.83..2064.93 rows=10 width=4) (actual time=6.902..6.904 rows=10 loops=1)
              Group Key: prodcats.product_id
              ->  Limit  (cost=2064.02..2064.71 rows=10 width=74) (actual time=6.893..6.895 rows=10 loops=1)
                    ->  Index Only Scan using idx_prodcats_1 on prodcats  (cost=0.56..2860.10 rows=41574 width=74) (actual time=0.010..6.173 rows=30010 loops=1)
                          Index Cond: (category_id = 248688)
                          Heap Fetches: 0
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.003..0.003 rows=1 loops=10)
              Index Cond: (id = prodcats.product_id)
Planning time: 0.318 ms
Execution time: 7.066 ms

And this is the worst scenario with large number of products in chosen category and large offset. When offset=300 execution time is just 0.5 ms.

Unfortunately maintaining such a junction table requires extra effort. It could be accomplished via indexed materialized views,
but that is only useful when your data updates rarely, cause refreshing such materialized view is quite a heavy operation.

So I am staying with GIN index so far, with increased work_mem and reduced memory footprint query.


Where clause over multiple rows, GROUP BY

The design pattern is called EAV (entity-attribute-value). It's usual to need either GROUP BY or queries with multiple joins with this design:

SELECT
      a.AccountID
FROM
      tbl AS a
      JOIN tbl AS b
      ON a.AccountID = b.AccountID
WHERE
      a.Property = 'Status'
  AND a.Value    = 'Active'

  AND b.Property = 'RegistrationDate'
  AND b.Value    = '2018-05-11 11:30:14.000' ;