pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDesperate: View not using indexes (very slow)
2+ messages / 2 participants
[nested] [flat]
* Desperate: View not using indexes (very slow)
@ 2006-01-25 16:09 Jen Sale <js@slipt.net>
0 siblings, 1 reply; 2+ messages in thread
From: Jen Sale @ 2006-01-25 16:09 UTC (permalink / raw)
To: pgsql-performance
We recently segmented a large table into calendar month slices and were going
to to replace the original, but we are not getting the results we think it
should... Everything is vacuumed, and we are using 8.0.3 on amd64.
Anything anyone can suggest would be appreciated, our backs against the wall.
=> explain select suck_id from sucks_new where suck_id in ( select id as
suck_id from saved_cart_items where
publish_id='60160b57a1969fa228ae3470fbe7a50a' );
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=5311290.80..7124499.86 rows=5472 width=32)
Join Filter: ("outer".suck_id = "inner".id)
-> Subquery Scan sucks_new (cost=5309907.40..6181642.53 rows=13947762
width=32)
-> Unique (cost=5309907.40..6042164.91 rows=13947762 width=212)
-> Sort (cost=5309907.40..5344776.81 rows=13947762 width=212)
Sort Key: suck_id, sitenum
-> Append (cost=0.00..632289.24 rows=13947762
width=212)
-> Subquery Scan "*SELECT*
1" (cost=0.00..83767.54 rows=1703577 width=209)
-> Seq Scan on sucks_2006_01
(cost=0.00..66731.77 rows=1703577 width=209)
-> Subquery Scan "*SELECT*
2" (cost=0.00..93670.20 rows=2081560 width=209)
-> Seq Scan on sucks_2005_12
(cost=0.00..72854.60 rows=2081560 width=209)
-> Subquery Scan "*SELECT*
3" (cost=0.00..91311.16 rows=2021958 width=210)
-> Seq Scan on sucks_2005_11
(cost=0.00..71091.58 rows=2021958 width=210)
-> Subquery Scan "*SELECT*
4" (cost=0.00..85510.34 rows=1886417 width=211)
-> Seq Scan on sucks_2005_10
(cost=0.00..66646.17 rows=1886417 width=211)
-> Subquery Scan "*SELECT*
5" (cost=0.00..74216.38 rows=1642719 width=210)
-> Seq Scan on sucks_2005_09
(cost=0.00..57789.19 rows=1642719 width=210)
-> Subquery Scan "*SELECT*
6" (cost=0.00..64346.12 rows=1429106 width=209)
-> Seq Scan on sucks_2005_08
(cost=0.00..50055.06 rows=1429106 width=209)
-> Subquery Scan "*SELECT*
7" (cost=0.00..76449.66 rows=1709283 width=209)
-> Seq Scan on sucks_2005_07
(cost=0.00..59356.83 rows=1709283 width=209)
-> Subquery Scan "*SELECT*
8" (cost=0.00..63017.84 rows=1473142 width=212)
-> Seq Scan on sucks_2005_06
"local" (cost=0.00..48286.42 rows=1473142 width=212)
-> Materialize (cost=1383.39..1383.60 rows=20 width=12)
-> Seq Scan on saved_cart_items (cost=0.00..1383.38 rows=20
width=12)
Filter: (publish_id =
'60160b57a1969fa228ae3470fbe7a50a'::bpchar)
as opposed to
=> explain select suck_id from sucks_new where suck_id=7136642;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan sucks_new (cost=46.22..46.72 rows=8 width=32)
-> Unique (cost=46.22..46.64 rows=8 width=212)
-> Sort (cost=46.22..46.24 rows=8 width=212)
Sort Key: suck_id, sitenum
-> Append (cost=0.00..46.10 rows=8 width=212)
-> Subquery Scan "*SELECT* 1" (cost=0.00..5.64 rows=1
width=209)
-> Index Scan using sucks_2006_01_pkey on
sucks_2006_01 (cost=0.00..5.63 rows=1 width=209)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 2" (cost=0.00..5.95 rows=1
width=209)
-> Index Scan using sucks_2005_12_pkey on
sucks_2005_12 (cost=0.00..5.94 rows=1 width=209)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 3" (cost=0.00..5.21 rows=1
width=210)
-> Index Scan using sucks_2005_11_pkey on
sucks_2005_11 (cost=0.00..5.20 rows=1 width=210)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 4" (cost=0.00..5.67 rows=1
width=211)
-> Index Scan using sucks_2005_10_pkey on
sucks_2005_10 (cost=0.00..5.66 rows=1 width=211)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 5" (cost=0.00..5.78 rows=1
width=210)
-> Index Scan using sucks_2005_09_pkey on
sucks_2005_09 (cost=0.00..5.77 rows=1 width=210)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 6" (cost=0.00..6.01 rows=1
width=209)
-> Index Scan using sucks_2005_08_pkey on
sucks_2005_08 (cost=0.00..6.00 rows=1 width=209)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 7" (cost=0.00..5.87 rows=1
width=209)
-> Index Scan using sucks_2005_07_pkey on
sucks_2005_07 (cost=0.00..5.86 rows=1 width=209)
Index Cond: (suck_id = 7136642::numeric)
-> Subquery Scan "*SELECT* 8" (cost=0.00..5.98 rows=1
width=212)
-> Index Scan using sucks_2005_06_pkey on
sucks_2005_06 "local" (cost=0.00..5.97 rows=1 width=212)
Index Cond: (suck_id = 7136642::numeric)
(29 rows)
can someone please tell me what we did wrong?
TIA
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Desperate: View not using indexes (very slow)
@ 2006-01-30 03:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jen Sale <js@slipt.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-30 03:50 UTC (permalink / raw)
To: Jen Sale <js@slipt.net>; +Cc: pgsql-performance
Jen Sale <js@slipt.net> writes:
> can someone please tell me what we did wrong?
Joins against union subqueries aren't handled very well at the moment.
(As it happens, I'm working on that exact problem right now for 8.2,
but that won't help you today.)
The plan indicates that you are using UNION rather than UNION ALL,
which is not helping any. Do you really need duplicate elimination
in that view?
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-30 03:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-25 16:09 Desperate: View not using indexes (very slow) Jen Sale <js@slipt.net>
2006-01-30 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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