Hot answers tagged query-performance
8
I am assuming data type text for the relevant columns.
CREATE TABLE prefix (code text, name text, price int);
CREATE TABLE num (number text, time int);
"Simple" Solution
SELECT DISTINCT ON (1)
n.number, p.code
FROM num n
JOIN prefix p ON right(n.number, -1) LIKE (p.code || '%')
ORDER BY n.number, p.code DESC;
Key elements:
DISTINCT ON is a ...
4
Some notes:
After rebuilding indexes, do not update all statistics!
REBUILD indexes, don't REORGANISE
An index rebuild will rebuild statistics anyway. A further update actually means you'll have worse statistics because of sampling ratio. You can rebuild column statistics though.
Also, it's worth comparing the server specs (RAM, CPU, Disk) and query ...
2
One option would look something like this:
SELECT a.* FROM account a
LEFT JOIN extra_info ei ON ei.account_id = a.id AND ei.data_key = 'test'
WHERE ei.account_id IS NULL;
This might seem counter-intuitive, since "obviously" er.account_id is never actually NULL sitting at rest in the table, so I'll explain:
The LEFT JOIN of course means all rows from ...
2
You're only considering that there could be zero or one joined rows in ind3 -- if there were seven then it would affect the number of rows returned from the query, so it's not correct to say that the join is irrelevant.
Even if that were not the case, it's a matter of whether a potential optimising operation is implemented in the query optimiser. Recent ...
1
Under the covers clustered and nonclustered indexes are the same. The clustered index just has the additional property that is is guaranteed to INCLUDE all columns. Therefore the data does not need to be maintained somewhere else. So, a clustered index and a nonclustered index that INCLUDEs all columns are virtually the same from an update cost perspective.
...
1
Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.
First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no ...
1
What kind of column is [registered] -> datetime or varchar
Why are you grouping by the full value of the [registered] column ?
Ex. If you have the following values for this column:
2013-05-01 00:00:00
2013-05-01 00:00:01
2013-05-01 00:00:02
2013-05-01 00:00:03
2013-05-01 00:00:04
... and you group by them, you will have 5 rows in the ...
1
SQL Fiddle
with items as (
select registered, category_id
from items
where
category_id is not null
and
registered < '2013-05-02'
), items_restricted as (
select registered, category_id
from items
where registered >= '2013-05-01'
), category_id as (
select distinct category_id
from ...
Only top voted, non community-wiki answers of a minimum length are eligible
