CODEWITHSUNDEEP
postgresql
Mike S
Mike S

Reputation: 1613

Understanding SQL query complexity

I'm currently having trouble understanding why a seemingly simple query is taking much longer to return results than a much more complicated (looking) query.

I have a view, performance_summary (which in turn selects from another view). Currently, within psql, when I run a query like 

SELECT section 
FROM performance_summary 
LIMIT 1;

it takes a minute or so to return a result, whereas a query like

SELECT section, version, weighted_approval_rate 
FROM performance_summary 
WHERE version in ('1.3.10', '1.3.11') AND section ~~ '%WEST' 
ORDER BY 1,2;

gets results almost instantly. Without knowing how the view is defined, is there any obvious or common reason why this is?

Upvotes: 0

Views: 174

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

Not really, without knowing how the view is defined. It could be that the "more complex" query uses an index to select just two rows and then perform some trivial grouping sorting on the two. The query without the where clause might see postgres operating on millions of rows, trillions of operations and producing a single row out after discarding 999999999 rows, we just don't know unless you post the view definition and the explain plan output for both queries

You might be falling into the trap of thinking that a View is somehow a cache of info - it isn't. It's a stored query, that is inserted into the larger query when you select from it/include it in another query- this means that the whole thing must be planned and executed from scratch. There isn't a notion that creating a View does any pre planning etc, onto which other further improvement is done. It's more like the definition of the View is pasted into any query that uses it, then the query is run as if it were just written there and then

Upvotes: 2

Related Questions