Tag Info

Hot answers tagged

2

Here is the query to order it the way the question has it SELECT c.cat_name,p.prod_name FROM products p INNER JOIN category c ON p.cat_id = c.cat_id ORDER BY prod_name; Here is that query formatted SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine FROM products p INNER JOIN category c ON p.cat_id = c.cat_id ORDER BY ...


2

The following indexes may help you: CREATE INDEX stkx_custloc_ind1 ON customer_location USING btree (location_id, customer_id); CREATE INDEX stkx_custrecpt_ind1 ON customer_receipt USING btree (customer_id, receipt_id); CREATE INDEX stkx_recptprod_ind1 ON receipt_product USING btree (receipt_id, status COLLATE pg_catalog."default", ...


2

This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that. You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are ...


2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


1

Most likely the statistics on the field were out of date, adding the index create/updated statistics with a full table scan. More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


1

Just looking at your definitions (thank you very much for that) I see: No index on to support WHERE dbo.EntryTypes.EntryType = 'Error'. (But this may not be needed if it is a small table with just a few entries.) Your fk_* columns in MAIN have check constraints, but no indexes. You should create some indexes since they are used to join to several ...


1

My diagnosis from the information that is currently available: Sounds like your tables are for whatever reason mostly uncached. The first run brings all data into cache. The plans probably have tons of random IO. This is extremely slow to run on a disk, and almost unnoticeable in memory. Therefore, I guess that the new edition does not provide you as much ...


1

Multiplying the rows is invalid for several reasons: Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but ...


1

As you are repeating this query for multiple months then you will be continually re-aggregating the same rows. For example the rows in the first month will always be brought back by the t1.post_date < @report_date criteria so will be re-processed for every month. To avoid this I'd probably consider working through it in an iterative way a month at a ...


1

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types. For example: SELECT idclient AS [Client ID] ,COUNT(value) AS [Total Orders] ,SUM(value) AS [Total Order Value] ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1] ...


1

Since phone_number is the field with high cardinality, I would try putting an index on that. If your performance is still poor, I would try adding campaign_id and the other output fields (id, last_attempt_time, call_time) to your index, so there's no need to hit the actual table at all.


1

If the IN value list is only up to 3 items, you can rewrite with UNION. The query will have to be build dynamically, either in some other language or with dynamic SQL but it will have only equiality conditions and should not do a table scan: SELECT id, phone_number, last_attempt_time FROM ( SELECT id, phone_number, last_attempt_time, call_time FROM ...


1

I'm not a Postgres guy, but with other DBs this can be a problem with the table statistics that the query planner relies on. Resetting those via an ANALYZE TABLE can resolve the issue. Your queries & indexes look correct. http://www.postgresql.org/docs/9.0/static/sql-analyze.html



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