pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery on view radically slower than query on underlying table
5+ messages / 3 participants
[nested] [flat]
* Query on view radically slower than query on underlying table
@ 2011-02-28 18:28 Craig James <craig_james@emolecules.com>
2011-02-28 18:57 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Craig James @ 2011-02-28 18:28 UTC (permalink / raw)
To: pgsql-performance
We have a medium-sized catalog (about 5 million rows), but some of our customers only want to see portions of it. I've been experimenting with a customer-specific schema that contains nothing but a "join table" -- just the primary keys of that portion of the data that each customer wants to see, which is used to create a view that looks like the original table. But the most important query, the one that customers use to scan page-by-page through search results, turns out to be far too slow (65 seconds versus 55 milliseconds).
Below are the results of two explain/analyze statements. The first one uses the view, the second one goes directly to the original tables. I thought this would be a slam-dunk, that it would return results in a flash because the view is created from two tables with the same primary keys.
My guess (and it's just a wild guess) is that the "left join" is forcing a sequence scan or something. But we need the left join, because it's on a "hitlist" that recorded all the matches to a customer's earlier query, and if rows have been removed from the tables, the customer needs to see a blank row.
Here is the "bad" query, which is run on the view:
em=> explain analyze
select version.version_id, version.isosmiles
from hitlist_rows_reset_140
left join version on (hitlist_rows_reset_140.objectid = version.version_id)
where hitlist_rows_reset_140.sortorder >= 1
and hitlist_rows_reset_140.sortorder <= 10
order by hitlist_rows_reset_140.sortorder;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
------------------------------
Nested Loop Left Join (cost=23687.51..215315.74 rows=1 width=54) (actual time=2682.662..63680.076 rows=10 loops=1)
Join Filter: (hitlist_rows_reset_140.objectid = v.version_id)
-> Index Scan using hitlist_rows_reset_140_pkey on hitlist_rows_reset_140 (cost=0.00..8.36 rows=1 width=8) (actual time=
0.015..0.049 rows=10 loops=1)
Index Cond: ((sortorder >= 1) AND (sortorder <= 10))
-> Hash Join (cost=23687.51..204666.54 rows=851267 width=50) (actual time=31.829..6263.403 rows=851267 loops=10)
Hash Cond: (v.version_id = mv.version_id)
-> Seq Scan on version v (cost=0.00..116146.68 rows=5631968 width=50) (actual time=0.006..859.758 rows=5632191 loo
ps=10)
-> Hash (cost=13046.67..13046.67 rows=851267 width=4) (actual time=317.488..317.488 rows=851267 loops=1)
-> Seq Scan on my_version mv (cost=0.00..13046.67 rows=851267 width=4) (actual time=2.888..115.166 rows=8512
67 loops=1)
Total runtime: 63680.162 ms
Here is the "good" query, which is run directly on the data tables.
em=> explain analyze
select registry.version.version_id, registry.version.isosmiles
from hitlist_rows_reset_140
left join registry.version on (hitlist_rows_reset_140.objectid = registry.version.version_id)
where hitlist_rows_reset_140.sortorder >= 1
and hitlist_rows_reset_140.sortorder <= 10
order by hitlist_rows_reset_140.SortOrder;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
------------------------------
Nested Loop Left Join (cost=0.00..17.73 rows=1 width=54) (actual time=36.022..55.558 rows=10 loops=1)
-> Index Scan using hitlist_rows_reset_140_pkey on hitlist_rows_reset_140 (cost=0.00..8.36 rows=1 width=8) (actual time=
0.021..0.025 rows=10 loops=1)
Index Cond: ((sortorder >= 1) AND (sortorder <= 10))
-> Index Scan using version_pkey on version (cost=0.00..9.35 rows=1 width=50) (actual time=5.551..5.552 rows=1 loops=10)
Index Cond: (hitlist_rows_reset_140.objectid = version.version_id)
Total runtime: 55.608 ms
(6 rows)
The view is defined like this:
em=> \d my_version
Table "test_schema.my_version"
Column | Type | Modifiers
------------+---------+-----------
version_id | integer | not null
Indexes:
"my_version_pkey" PRIMARY KEY, btree (version_id)
em=> \d version
View "test_schema.version"
Column | Type | Modifiers
------------+---------+-----------
version_id | integer |
parent_id | integer |
isosmiles | text |
coord_2d | text |
View definition:
SELECT v.version_id, v.parent_id, v.isosmiles, v.coord_2d
FROM registry.version v
JOIN my_version mv ON mv.version_id = v.version_id;
This is:
Postgres 8.4.4
Ubuntu Linux 2.6.32-27
Database: 8x7200 RAID 10, LSI RAID controller with BBU
WAL: 2x7200 RAID1
Non-default config parameters:
max_connections = 500
shared_buffers = 1000MB
work_mem = 128MB
synchronous_commit = off
full_page_writes = off
wal_buffers = 256kB
checkpoint_segments = 30
effective_cache_size = 4GB
track_activities = on
track_counts = off
track_functions = none
escape_string_warning = off
Thanks,
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query on view radically slower than query on underlying table
2011-02-28 18:28 Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
@ 2011-02-28 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-28 19:23 ` Re: Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-02-28 18:57 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> My guess (and it's just a wild guess) is that the "left join" is
> forcing a sequence scan or something.
No, that's forcing the other join to be done in toto because it can't
reorder the left join and regular join.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query on view radically slower than query on underlying table
2011-02-28 18:28 Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
2011-02-28 18:57 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-02-28 19:23 ` Craig James <craig_james@emolecules.com>
2011-02-28 19:58 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Craig James @ 2011-02-28 19:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> Craig James<craig_james@emolecules.com> writes:
>> Here is the "bad" query, which is run on the view:
>>
>> em=> explain analyze
>> select version.version_id, version.isosmiles
>> from hitlist_rows_reset_140
>> left join version on (hitlist_rows_reset_140.objectid = version.version_id)
>> where hitlist_rows_reset_140.sortorder >= 1
>> and hitlist_rows_reset_140.sortorder <= 10
>> order by hitlist_rows_reset_140.sortorder;
>> QUERY PLAN
>>
>> -----------------------------------------------------------------------------------------------------------------------------
>> ------------------------------
>> Nested Loop Left Join (cost=23687.51..215315.74 rows=1 width=54) (actual time=2682.662..63680.076 rows=10 loops=1)
>> Join Filter: (hitlist_rows_reset_140.objectid = v.version_id)
>> -> Index Scan using hitlist_rows_reset_140_pkey on hitlist_rows_reset_140 (cost=0.00..8.36 rows=1 width=8) (actual time=
>> 0.015..0.049 rows=10 loops=1)
>> Index Cond: ((sortorder >= 1) AND (sortorder <= 10))
>> -> Hash Join (cost=23687.51..204666.54 rows=851267 width=50) (actual time=31.829..6263.403 rows=851267 loops=10)
>> Hash Cond: (v.version_id = mv.version_id)
>> -> Seq Scan on version v (cost=0.00..116146.68 rows=5631968 width=50) (actual time=0.006..859.758 rows=5632191 loo
>> ps=10)
>> -> Hash (cost=13046.67..13046.67 rows=851267 width=4) (actual time=317.488..317.488 rows=851267 loops=1)
>> -> Seq Scan on my_version mv (cost=0.00..13046.67 rows=851267 width=4) (actual time=2.888..115.166 rows=8512
>> 67 loops=1)
>> Total runtime: 63680.162 ms
On 2/28/11 10:57 AM, Tom Lane wrote:
>> My guess (and it's just a wild guess) is that the "left join" is
>> forcing a sequence scan or something.
>
> No, that's forcing the other join to be done in toto because it can't
> reorder the left join and regular join.
I change the "left join" to just "join" and confirmed that it's fast -- the join on the view drops from 65 seconds back down to a few milliseconds.
Then I thought maybe putting a foreign-key constraint on table "my_version" would solve the problem:
alter table my_version add constraint fk_my_view foreign key(version_id)
references registry.version(version_id) on delete cascade;
That way, the planner would know that every key in table "my_version" has to also be in table "version", thus avoiding that part about "forcing the other join to be done in toto". But the foreign-key constraint makes no difference, it still does the full join and takes 65 seconds.
So here's how I see it:
- The select can only return ten rows from table "hitlist_rows_reset_140"
- The left join could be applied to table "my_version"
- The results of that could be joined to table "version"
It seems to me that with the foreign-key constraint, it shouldn't have to examine more than ten rows from any of the three tables. Or have I overlooked something?
Thanks,
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query on view radically slower than query on underlying table
2011-02-28 18:28 Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
2011-02-28 18:57 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
2011-02-28 19:23 ` Re: Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
@ 2011-02-28 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-02 15:39 ` Re: Query on view radically slower than query on underlying table Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-02-28 19:58 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> Then I thought maybe putting a foreign-key constraint on table "my_version" would solve the problem:
> alter table my_version add constraint fk_my_view foreign key(version_id)
> references registry.version(version_id) on delete cascade;
> That way, the planner would know that every key in table "my_version" has to also be in table "version", thus avoiding that part about "forcing the other join to be done in toto". But the foreign-key constraint makes no difference, it still does the full join and takes 65 seconds.
That's just wishful thinking I'm afraid. The planner doesn't currently
make any deductions whatsoever from the presence of a foreign key
constraint; and even if it did, I'm not sure that this would help it
decide that a join order constraint could safely be dropped.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query on view radically slower than query on underlying table
2011-02-28 18:28 Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
2011-02-28 18:57 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
2011-02-28 19:23 ` Re: Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
2011-02-28 19:58 ` Re: Query on view radically slower than query on underlying table Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-03-02 15:39 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Haas @ 2011-03-02 15:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On Mon, Feb 28, 2011 at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Craig James <craig_james@emolecules.com> writes:
>> Then I thought maybe putting a foreign-key constraint on table "my_version" would solve the problem:
>
>> alter table my_version add constraint fk_my_view foreign key(version_id)
>> references registry.version(version_id) on delete cascade;
>
>> That way, the planner would know that every key in table "my_version" has to also be in table "version", thus avoiding that part about "forcing the other join to be done in toto". But the foreign-key constraint makes no difference, it still does the full join and takes 65 seconds.
>
> That's just wishful thinking I'm afraid. The planner doesn't currently
> make any deductions whatsoever from the presence of a foreign key
> constraint; and even if it did, I'm not sure that this would help it
> decide that a join order constraint could safely be dropped.
I've previously mused on -hackers about teaching the planner the
concept of an inner-or-left-join; that is, a join that's guaranteed to
return the same results whichever way we choose to implement it.
Proving that an inner join is actually inner-or-left would allow the
join removal logic to consider removing it altogether, and would allow
reordering in cases that aren't otherwise known to be safe. Proving
that a left join is actually inner-or-left doesn't help with join
removal, but it might allow the join to be reordered. Maybe
"non-row-reducing-join" is better terminology than
"inner-or-left-join", but in any case I have a suspicion that inner
join removal will end up being implemented as a special case of
noticing that an inner join falls into this class.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-03-02 15:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-28 18:28 Query on view radically slower than query on underlying table Craig James <craig_james@emolecules.com>
2011-02-28 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-28 19:23 ` Craig James <craig_james@emolecules.com>
2011-02-28 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-02 15:39 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox