pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique index and estimated rows.
3+ messages / 2 participants
[nested] [flat]
* Unique index and estimated rows.
@ 2004-01-30 09:15 Kari Lavikka <tuner@bdb.fi>
2004-01-31 01:45 ` Re: Unique index and estimated rows. Josh Berkus <josh@agliodbs.com>
2004-01-31 08:52 ` Re: Unique index and estimated rows. Kari Lavikka <tuner@bdb.fi>
0 siblings, 2 replies; 3+ messages in thread
From: Kari Lavikka @ 2004-01-30 09:15 UTC (permalink / raw)
To: pgsql-performance
Hi, more strange plans ...
Planner estimates an indexscan to return 240 rows although it is using a
unique index and chooses to use hash join and seqscan instead of nested
loop and indexscan. It's ... very slow.
Idexes used:
users: "users_upper_nick" unique, btree (upper((nick)::text))
image: "image_uid_status" btree (uid, status)
galleria=> set enable_hashjoin = true;
SET
galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=24731.07..24734.95 rows=1550 width=63) (actual
time=1392.675..1392.686 rows=19 loops=1)
Sort Key: i.status, i.stamp
-> Hash Join (cost=961.31..24648.94 rows=1550 width=63) (actual time=552.184..1392.617 rows=19 loops=1)
Hash Cond: ("outer".uid = "inner".uid)
-> Seq Scan on image i (cost=0.00..22025.22 rows=329382 width=53) (actual time=0.009..1088.856 rows=346313 loops=1)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
-> Hash (cost=960.71..960.71 rows=240 width=14) (actual time=0.043..0.043 rows=0 loops=1)
-> Index Scan using users_upper_nick on users u (cost=0.00..960.71 rows=240 width=14) (actual time=0.037..0.039 rows=1 loops=1)
Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
Filter: (status = 'a'::bpchar)
Total runtime: 1392.769 ms
(11 rows)
galleria=> set enable_hashjoin = false;
SET
galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=35861.87..35865.74 rows=1550 width=63) (actual
time=0.230..0.244 rows=19 loops=1)
Sort Key: i.status, i.stamp
-> Nested Loop (cost=0.00..35779.73 rows=1550 width=63) (actual time=0.070..0.173 rows=19 loops=1)
-> Index Scan using users_upper_nick on users u (cost=0.00..960.71 rows=240 width=14) (actual time=0.036..0.038 rows=1 loops=1)
Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
Filter: (status = 'a'::bpchar)
-> Index Scan using image_uid_status on image i (cost=0.00..144.83 rows=20 width=53) (actual time=0.026..0.080 rows=19 loops=1)
Index Cond: (i.uid = "outer".uid)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
Total runtime: 0.315 ms
(10 rows)
|\__/|
( oo ) Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unique index and estimated rows.
2004-01-30 09:15 Unique index and estimated rows. Kari Lavikka <tuner@bdb.fi>
@ 2004-01-31 01:45 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2004-01-31 01:45 UTC (permalink / raw)
To: Kari Lavikka <tuner@bdb.fi>; pgsql-performance
Kari,
> users: "users_upper_nick" unique, btree (upper((nick)::text))
> image: "image_uid_status" btree (uid, status)
Odd ... when did you last run ANALYZE on "users"?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unique index and estimated rows.
2004-01-30 09:15 Unique index and estimated rows. Kari Lavikka <tuner@bdb.fi>
@ 2004-01-31 08:52 ` Kari Lavikka <tuner@bdb.fi>
1 sibling, 0 replies; 3+ messages in thread
From: Kari Lavikka @ 2004-01-31 08:52 UTC (permalink / raw)
To: pgsql-performance
Uh oh,
function indexes seem to be a bit crippled. I created a unique index
without the upper() function and number of estimated rows is now just
right.
"users_nick" unique, btree (nick)
And the plan:
galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND nick = 'FireGirl-' AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=154.10..154.12 rows=7 width=63) (actual time=0.227..0.237 rows=19 loops=1)
Sort Key: i.status, i.stamp
-> Nested Loop (cost=0.00..154.00 rows=7 width=63) (actual time=0.075..0.176 rows=19 loops=1)
-> Index Scan using users_nick on users u (cost=0.00..6.01 rows=1 width=14) (actual time=0.041..0.043 rows=1 loops=1)
Index Cond: ((nick)::text = 'FireGirl-'::text)
Filter: (status = 'a'::bpchar)
-> Index Scan using image_uid_status on image i (cost=0.00..147.73 rows=21 width=53) (actual time=0.026..0.079 rows=19 loops=1)
Index Cond: (i.uid = "outer".uid)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
Total runtime: 0.303 ms
(10 rows)
I think that creating an uppercase column for name and unique index for
could be a workaround for this.
Another problem is that function indexes don't seem to care about
statistics target settings.
|\__/|
( oo ) Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""
On Fri, 30 Jan 2004, Kari Lavikka wrote:
>
> Hi, more strange plans ...
>
> Planner estimates an indexscan to return 240 rows although it is using a
> unique index and chooses to use hash join and seqscan instead of nested
> loop and indexscan. It's ... very slow.
>
> Idexes used:
> users: "users_upper_nick" unique, btree (upper((nick)::text))
> image: "image_uid_status" btree (uid, status)
>
> galleria=> set enable_hashjoin = true;
> SET
> galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=24731.07..24734.95 rows=1550 width=63) (actual
> time=1392.675..1392.686 rows=19 loops=1)
> Sort Key: i.status, i.stamp
> -> Hash Join (cost=961.31..24648.94 rows=1550 width=63) (actual time=552.184..1392.617 rows=19 loops=1)
> Hash Cond: ("outer".uid = "inner".uid)
> -> Seq Scan on image i (cost=0.00..22025.22 rows=329382 width=53) (actual time=0.009..1088.856 rows=346313 loops=1)
> Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
> -> Hash (cost=960.71..960.71 rows=240 width=14) (actual time=0.043..0.043 rows=0 loops=1)
> -> Index Scan using users_upper_nick on users u (cost=0.00..960.71 rows=240 width=14) (actual time=0.037..0.039 rows=1 loops=1)
> Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
> Filter: (status = 'a'::bpchar)
> Total runtime: 1392.769 ms
> (11 rows)
>
> galleria=> set enable_hashjoin = false;
> SET
> galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=35861.87..35865.74 rows=1550 width=63) (actual
> time=0.230..0.244 rows=19 loops=1)
> Sort Key: i.status, i.stamp
> -> Nested Loop (cost=0.00..35779.73 rows=1550 width=63) (actual time=0.070..0.173 rows=19 loops=1)
> -> Index Scan using users_upper_nick on users u (cost=0.00..960.71 rows=240 width=14) (actual time=0.036..0.038 rows=1 loops=1)
> Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
> Filter: (status = 'a'::bpchar)
> -> Index Scan using image_uid_status on image i (cost=0.00..144.83 rows=20 width=53) (actual time=0.026..0.080 rows=19 loops=1)
> Index Cond: (i.uid = "outer".uid)
> Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
> Total runtime: 0.315 ms
> (10 rows)
>
>
> |\__/|
> ( oo ) Kari Lavikka - tuner@bdb.fi - (050) 380 3808
> __ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
> ""
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-31 08:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-30 09:15 Unique index and estimated rows. Kari Lavikka <tuner@bdb.fi>
2004-01-31 01:45 ` Josh Berkus <josh@agliodbs.com>
2004-01-31 08:52 ` Kari Lavikka <tuner@bdb.fi>
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