Skip site navigation (1) Skip section navigation (2)

Problems with adding a is not null to a query.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problems with adding a is not null to a query.
Date: 2011-01-15 13:14:32
Message-ID: AANLkTi=1DVjv6XdGSZ8H_LGvKdLBME4Gp15O0xnWv_8M@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I reported this in the pgsql-general list and was instructed to send
the analaze outputs here.

have this query it runs reasonably quickly (but should be quicker IMHO)


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

Here is the analyze for it.

"Limit  (cost=29903.44..29903.46 rows=10 width=1880) (actual
time=44.730..44.730 rows=0 loops=1)"
"  ->  Sort  (cost=29903.44..29910.04 rows=2642 width=1880) (actual
time=44.722..44.722 rows=0 loops=1)"
"        Sort Key: consolidated_urls.index_delta"
"        Sort Method:  quicksort  Memory: 17kB"
"        ->  Nested Loop  (cost=105.29..29846.34 rows=2642 width=1880)
(actual time=44.639..44.639 rows=0 loops=1)"
"              ->  Bitmap Heap Scan on topical_urls
(cost=105.29..7494.32 rows=2642 width=4) (actual time=44.635..44.635
rows=0 loops=1)"
"                    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..104.63 rows=2643 width=0) (actual time=44.629..44.629
rows=0 loops=1)"
"                          Index Cond: (domain_id = 157)"
"              ->  Index Scan using consolidated_urls_pkey on
consolidated_urls  (cost=0.00..8.45 rows=1 width=1880) (never
executed)"
"                    Index Cond: (consolidated_urls.id =
topical_urls.consolidated_url_id)"
"Total runtime: 45.023 ms"


I add another where clause and it becomes completely unusable.


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

Limit  (cost=0.00..20555.76 rows=10 width=1880) (actual
time=3152032.072..3152032.072 rows=0 loops=1)
  ->  Nested Loop  (cost=0.00..5430830.93 rows=2642 width=1880)
(actual time=3152032.065..3152032.065 rows=0 loops=1)
        ->  Index Scan Backward using
index_consolidateds_url_on_index_delta on consolidated_urls
(cost=0.00..5316175.98 rows=15242 width=1880) (actual
time=2928420.500..3151811.125 rows=14985 loops=1)
              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.51 rows=1 width=4) (actual
time=0.011..0.011 rows=0 loops=14985)
              Index Cond: ((topical_urls.domain_id = 157) AND
(topical_urls.consolidated_url_id = consolidated_urls.id))
              Filter: (NOT topical_urls.hidden)
 Total runtime: 3152066.335 ms
(8 rows)


I tried putting another indexed field in the query instead of
index_delta but it didn't help.

Hope this helps you guys figure out what the issue is.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-01-15 15:45:03
Subject: Re: Problems with adding a is not null to a query.
Previous:From: Dave PageDate: 2011-01-15 07:57:50
Subject: Re: BUG #5839: pgAdmin makes bad query for creating a table when default value specified

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group