From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Adding an "and is not null" on an indexed field slows the query down immensely. |
Date: | 2011-01-15 07:25:39 |
Message-ID: | AANLkTimUFFJ=Yaj4BPmUwrQtNbN+3TC8sV+Ht7KS8niC@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have this query it runs reasonably quickly.
SELECT "consolidated_urls".* FROM "consolidated_urls" INNER JOIN "topical_urls"
ON "consolidated_urls".id = "topical_urls".consolidated_url_id
WHERE (("topical_urls".domain_id = 157) AND ((topical_urls.hidden = 'f')))
ORDER BY index_delta DESC LIMIT 10
The explain for this goes like this.
"Limit (cost=29899.43..29899.46 rows=10 width=1880)"
" -> Sort (cost=29899.43..29906.04 rows=2642 width=1880)"
" Sort Key: consolidated_urls.index_delta"
" -> Nested Loop (cost=101.29..29842.34 rows=2642 width=1880)"
" -> Bitmap Heap Scan on topical_urls
(cost=101.29..7490.32 rows=2642 width=4)"
" Recheck Cond: (domain_id = 157)"
" Filter: (NOT hidden)"
" -> Bitmap Index Scan on
index_topical_urls_on_domain_id_and_consolidated_url_id
(cost=0.00..100.63 rows=2643 width=0)"
" Index Cond: (domain_id = 157)"
" -> Index Scan using consolidated_urls_pkey on
consolidated_urls (cost=0.00..8.45 rows=1 width=1880)"
" Index Cond: (consolidated_urls.id =
topical_urls.consolidated_url_id)"
I add one more clause on to it to filter out index_deltas that are not
null and the query becomes unusably slow.
SELECT "consolidated_urls".* FROM "consolidated_urls" INNER JOIN "topical_urls"
ON "consolidated_urls".id = "topical_urls".consolidated_url_id
WHERE (("topical_urls".domain_id = 157) AND ((topical_urls.hidden = 'f')))
AND (index_delta IS NOT NULL) ORDER BY index_delta DESC LIMIT 10
The explain for this is goes like this
"Limit (cost=0.00..20555.33 rows=10 width=1880)"
" -> Nested Loop (cost=0.00..5430717.58 rows=2642 width=1880)"
" -> Index Scan Backward using
index_consolidateds_url_on_index_delta on consolidated_urls
(cost=0.00..5316175.98 rows=15242 width=1880)"
" Filter: (index_delta IS NOT NULL)"
" -> Index Scan using
index_topical_urls_on_domain_id_and_consolidated_url_id on
topical_urls (cost=0.00..7.50 rows=1 width=4)"
" Index Cond: ((topical_urls.domain_id = 157) AND
(topical_urls.consolidated_url_id = consolidated_urls.id))"
" Filter: (NOT topical_urls.hidden)"
The index_delta field is double precision and is indexed.
Any suggestions as to how to make this query run faster?
From | Date | Subject | |
---|---|---|---|
Next Message | pasman pasmański | 2011-01-15 09:55:26 | Re: Adding an "and is not null" on an indexed field slows the query down immensely. |
Previous Message | John R Pierce | 2011-01-15 06:53:43 | Re: HA solution |