pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jen Sale <js@slipt.net>
To: pgsql-performance@postgresql.org
Subject: Desperate: View not using indexes (very slow)
Date: Wed, 25 Jan 2006 11:09:25 -0500
Message-ID: <200601251109.25876.js@slipt.net> (raw )
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
view thread (2+ messages) latest in thread
Message-ID: <200601251109.25876.js@slipt.net>
Permalink: ../200601251109.25876.js@slipt.net/
Also on: postgresql.org/message-id/200601251109.25876.js@slipt.net
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: js@slipt.net
Subject: Re: Desperate: View not using indexes (very slow)
In-Reply-To: <200601251109.25876.js@slipt.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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