pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSequence vs. Index Scan
13+ messages / 4 participants
[nested] [flat]
* Sequence vs. Index Scan
@ 2007-05-05 21:05 Aaron Bono <postgresql@aranya.com>
2007-05-05 21:30 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Aaron Bono @ 2007-05-05 21:05 UTC (permalink / raw)
To: pgsql-sql
I have two schemas, both with the exact same DDL. One performs great and
the other has problems with a specific query:
SELECT *
FROM
branch_active_vw
WHERE branch_id = get_branch_for_zip ( '22151' )
ORDER BY branch_name
;
I am not defining the view here because the explain plans show the real
problem. I can post the view as well if it will help.
The explain plans are as follows:
Fast Schema:
1. Sort (cost=17.50..17.51 rows=1 width=680) (actual time=
2838.583..2838.586 rows=1 loops=1)
2. Sort Key: branch.branch_name
3. -> Nested Loop Left Join (cost=0.00..17.49 rows=1 width=680) (actual
time=2838.060..2838.093 rows=1 loops=1)
4. Join Filter: ("inner".branch_group_id = "outer".branch_group_id)
5. -> Nested Loop (cost=0.00..11.45 rows=1 width=647) (actual
time=2837.776..2837.804 rows=1 loops=1)
6. -> Nested Loop (cost=0.00..7.88 rows=1 width=618) (actual
time=2837.697..2837.716 rows=1 loops=1)
7. Join Filter: ("inner".locale_id = "outer".locale_id)
8. -> Nested Loop (cost=0.00..6.86 rows=1 width=598)
(actual time=2837.666..2837.676 rows=1 loops=1)
9. Join Filter: ("inner".corp_id =
"outer".corp_id)
10. -> Index Scan using branch_pkey on branch
(cost=0.00..5.84 rows=1 width=560) (actual time=2837.621..2837.624 rows=1
loops=1)
11. Index Cond: (branch_id =
get_branch_for_zip('22151'::character varying))
12. Filter: ((start_day <= now()) AND
((end_day IS NULL) OR (end_day >= now())))
13. -> Seq Scan on corp (cost=0.00..1.01 rows=1
width=46) (actual time=0.015..0.017 rows=1 loops=1)
14. -> Seq Scan on locale (cost=0.00..1.01 rows=1
width=28) (actual time=0.014..0.016 rows=1 loops=1)
15. -> Index Scan using zip_cd_pkey on zip_cd
branch_address_zip_cd (cost=0.00..3.55 rows=1 width=37) (actual time=
0.066..0.069 rows=1 loops=1)
16. Index Cond: (branch_address_zip_cd.zip_cd_id =
"outer".branch_address_zip_id)
17. -> Seq Scan on branch_group (cost=0.00..1.07 rows=7 width=41)
(actual time=0.013..0.029 rows=7 loops=1)
18. SubPlan
19. -> Seq Scan on branch_area (cost=0.00..4.89 rows=1 width=6)
(actual time=0.132..0.137 rows=2 loops=1)
20. Filter: (branch_id = $0)
21. Total runtime: 2839.044 ms
Slow Schema:
Sort (cost=12.77..12.78 rows=1 width=1380) (actual time=
157492.513..157492.515 rows=1 loops=1)
1. Sort Key: branch.branch_name
2. -> Nested Loop Left Join (cost=0.00..12.76 rows=1 width=1380) (actual
time=130130.384..157492.484 rows=1 loops=1)
3. Join Filter: ("inner".branch_group_id = "outer".branch_group_id)
4. -> Nested Loop (cost=0.00..10.34 rows=1 width=1360) (actual
time=130130.157..157492.253 rows=1 loops=1)
5. Join Filter: ("inner".locale_id = "outer".locale_id)
6. -> Nested Loop (cost=0.00..9.31 rows=1 width=1340)
(actual time=130130.127..157492.213 rows=1 loops=1)
7. -> Nested Loop (cost=0.00..5.75 rows=1 width=1311)
(actual time=130130.042..157492.119 rows=1 loops=1)
8. Join Filter: ("inner".corp_id =
"outer".corp_id)
9. -> Seq Scan on branch (cost=0.00..4.72 rows=1
width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
10. Filter: ((start_day <= now()) AND
((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
get_branch_for_zip('22151'::character varying)))
11. -> Seq Scan on corp (cost=0.00..1.01 rows=1
width=38) (actual time=0.022..0.024 rows=1 loops=1)
12. -> Index Scan using zip_cd_pkey on zip_cd
branch_address_zip_cd (cost=0.00..3.55 rows=1 width=37) (actual time=
0.070..0.073 rows=1 loops=1)
13. Index Cond: (branch_address_zip_cd.zip_cd_id =
"outer".branch_address_zip_id)
14. -> Seq Scan on locale (cost=0.00..1.01 rows=1 width=28)
(actual time=0.013..0.015 rows=1 loops=1)
15. -> Seq Scan on branch_group (cost=0.00..1.01 rows=1 width=28)
(actual time=0.013..0.015 rows=1 loops=1)
16. SubPlan
17. -> Seq Scan on branch_area (cost=0.00..1.40 rows=1 width=5)
(actual time=0.077..0.084 rows=1 loops=1)
18. Filter: (branch_id = $0)
19. Total runtime: 157492.890 ms
The problem is that lines 10-12 on the fast schema show an index scan while
lines 9-10 of the slow schema show a sequence scan. The sequence scan of
the branch_id, combined with the rest of the filter takes forever. I have
checked and there IS an index, specifically a primary key index, on the
branch_id in both schemas so I cannot figure out why the optimizer is
looking at these differently. In fact, the table the branch_id comes from
has the exact same indices and foreign keys on both schemas.
Any direction would be deeply appreciated.
Thanks!
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
@ 2007-05-05 21:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-05 22:00 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 13+ messages in thread
From: Andrew Sullivan @ 2007-05-05 21:30 UTC (permalink / raw)
To: pgsql-sql
On Sat, May 05, 2007 at 04:05:52PM -0500, Aaron Bono wrote:
> I have two schemas, both with the exact same DDL. One performs great and
Do they have the same data?
> I have
> checked and there IS an index, specifically a primary key index, on the
> branch_id in both schemas so I cannot figure out why the optimizer is
> looking at these differently. In fact, the table the branch_id comes from
> has the exact same indices and foreign keys on both schemas.
Different data? Different statistics? Different numbers of
(possibly dead) rows?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.
--Jane Jacobs
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-05 21:30 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-05-05 22:00 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 00:40 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2007-05-05 22:00 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On 5/5/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>
> On Sat, May 05, 2007 at 04:05:52PM -0500, Aaron Bono wrote:
> > I have two schemas, both with the exact same DDL. One performs great
> and
>
> Do they have the same data?
They have different data. The fast one has about 150 rows and the slow one
has about 40 rows. The field in question here, the branch_id, is a
BIGSERIAL in both.
> I have
> > checked and there IS an index, specifically a primary key index, on the
> > branch_id in both schemas so I cannot figure out why the optimizer is
> > looking at these differently. In fact, the table the branch_id comes
> from
> > has the exact same indices and foreign keys on both schemas.
>
> Different data? Different statistics? Different numbers of
> (possibly dead) rows?
We don't allow deletes and updates are fairly infrequent. I also did a
vacuum analyze to no effect.
What can I do to get more statistics that would help?
Thanks for the help,
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-05 21:30 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-05 22:00 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
@ 2007-05-06 00:40 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-06 04:13 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Sullivan @ 2007-05-06 00:40 UTC (permalink / raw)
To: pgsql-sql
On Sat, May 05, 2007 at 05:00:53PM -0500, Aaron Bono wrote:
>
> They have different data. The fast one has about 150 rows and the slow one
> has about 40 rows. The field in question here, the branch_id, is a
> BIGSERIAL in both.
I'd be astonished if a table of 40 rows ever got index scanned. It's
probably more efficient to read the whole table. But it seems your
case may be strange.
> We don't allow deletes and updates are fairly infrequent. I also did a
> vacuum analyze to no effect.
How do you "not allow" deletes? Does this happen because of a failed
foreign key? If so, you can end up with dead tuples. I'd look at
the output of VACUUM VERBOSE to make sure you don't have a lot of
dead tuples. That said, I wonder if fiddling with the statistics on
your tables might help.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-05 21:30 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-05 22:00 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:40 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-05-06 04:13 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2007-05-06 04:13 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On 5/5/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>
> On Sat, May 05, 2007 at 05:00:53PM -0500, Aaron Bono wrote:
>
> > We don't allow deletes and updates are fairly infrequent. I also did a
> > vacuum analyze to no effect.
>
> How do you "not allow" deletes?
Permissions are set on the tables to only allow the application to do
select/insert/update. We use start/end dates to indicate that records are
no longer active and then have the application run against views that filter
out inactive records. It also allows "removal" of records sometime in the
future, gives us the ability to "undelete" records, and keeps us from having
to code the application to have to clean up because of foreign key
constraints. There are other advantages.
I'd look at
> the output of VACUUM VERBOSE to make sure you don't have a lot of
> dead tuples.
Fast Schema:
psql:vacuumverbose.sql:1: INFO: vacuuming "fast_schema.branch"
psql:vacuumverbose.sql:1: INFO: index "branch_pkey" now contains 150 row
versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
Slow Schema:
psql:vacuumverbose.sql:1: INFO: vacuuming "slow_schema.branch"
psql:vacuumverbose.sql:1: INFO: index "branch_pkey" now contains 29 row
versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
Like Tom said earlier, I really think it has something to do with the
volatile/stable indicator on the function but I changed that. The function
in the slow schema acts like it is still volatile while the fast schema is
obviously stable.
Is there another way to analyze how the database is using the function in
the selects? That or is there a way to just look at how the function is
being used by the optimizer?
I do appreciate the feedback.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
@ 2007-05-06 00:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 03:28 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
1 sibling, 2 replies; 13+ messages in thread
From: Tom Lane @ 2007-05-06 00:22 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
"Aaron Bono" <postgresql@aranya.com> writes:
> 9. -> Seq Scan on branch (cost=0.00..4.72 rows=1
> width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
> 10. Filter: ((start_day <= now()) AND
> ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> get_branch_for_zip('22151'::character varying)))
There is something *awfully* wacko about that entry --- the fact that
the cost estimate is less than 5 units means that the planner thinks
there's 4 or fewer pages; either that's way wrong or the
get_branch_for_zip function is taking enormous amounts of time per row.
Have you tried timing that function on its own?
One possible reason for the performance difference is if you have
get_branch_for_zip marked as stable in one database and volatile in the
other --- volatile would prevent it from being used in an indexqual as
you'd like.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-06 03:28 ` Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2007-05-06 03:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 5/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Aaron Bono" <postgresql@aranya.com> writes:
> > 9. -> Seq Scan on branch (cost=0.00..4.72rows=1
> > width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
> > 10. Filter: ((start_day <= now()) AND
> > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > get_branch_for_zip('22151'::character varying)))
>
> There is something *awfully* wacko about that entry --- the fact that
> the cost estimate is less than 5 units means that the planner thinks
> there's 4 or fewer pages; either that's way wrong or the
> get_branch_for_zip function is taking enormous amounts of time per row.
> Have you tried timing that function on its own?
Yes, the first thing I did was run it on its own. It takes about 2-6
seconds to run which is not great but acceptable and the functions are
marked as stable. It is the same +/- a second or two on each of the two
schemas. The function is a plpgsql function that executes a dynamic SQL
string. Would it be better to change it to a straight SQL function? Would
that help the planner? I may be able to change it if this will help.
One possible reason for the performance difference is if you have
> get_branch_for_zip marked as stable in one database and volatile in the
> other --- volatile would prevent it from being used in an indexqual as
> you'd like.
I just switched the functions in both schemas to be stable (before I posted
the original question), they were volatile. After doing that, the fast
schema worked great but the slow one is still under performing. I even went
back to the slow schema and dropped and recreated the function with no
discernible difference.
This is for PostgreSQL 8.1.3 and the results are the same on Windows and
Linux versions. The windows version is a restore from the Linux version's
backup.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-06 04:25 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Re: Sequence vs. Index Scan Jaime Casanova <systemguards@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2007-05-06 04:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 5/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Aaron Bono" <postgresql@aranya.com> writes:
> > 9. -> Seq Scan on branch (cost=0.00..4.72rows=1
> > width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
> > 10. Filter: ((start_day <= now()) AND
> > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > get_branch_for_zip('22151'::character varying)))
>
> There is something *awfully* wacko about that entry --- the fact that
> the cost estimate is less than 5 units means that the planner thinks
> there's 4 or fewer pages; either that's way wrong or the
> get_branch_for_zip function is taking enormous amounts of time per row.
> Have you tried timing that function on its own?
>
> One possible reason for the performance difference is if you have
> get_branch_for_zip marked as stable in one database and volatile in the
> other --- volatile would prevent it from being used in an indexqual as
> you'd like.
>
I verified it by putting a RAISE NOTICE in the function. The fast schema
runs the function twice (odd, I would think it would run only once). The
slow schema runs it 30 times (the number of records returned + 1). I know I
put the functions into both schemas as stable and even dropped and recreated
the function. Then I verified with EMS Manager and it tells me the DDL for
the function in the database is set to stable. Is there something I can do
to tell PostgreSQL that I really did mean stable?
Thanks for all the help,
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
@ 2007-05-06 04:37 ` Jaime Casanova <systemguards@gmail.com>
2007-05-06 18:45 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jaime Casanova @ 2007-05-06 04:37 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On 5/5/07, Aaron Bono <postgresql@aranya.com> wrote:
> On 5/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Aaron Bono" <postgresql@aranya.com> writes:
> > > 9. -> Seq Scan on branch (cost=0.00..4.72
> rows=1
> > > width=1281) (actual time= 130129.988..157492.057 rows=1 loops=1)
> > > 10. Filter: ((start_day
> <= now()) AND
> > > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > > get_branch_for_zip('22151'::character varying)))
> >
> > There is something *awfully* wacko about that entry --- the fact that
> > the cost estimate is less than 5 units means that the planner thinks
> > there's 4 or fewer pages; either that's way wrong or the
> > get_branch_for_zip function is taking enormous amounts of time per row.
> > Have you tried timing that function on its own?
> >
> > One possible reason for the performance difference is if you have
> > get_branch_for_zip marked as stable in one database and volatile in the
> > other --- volatile would prevent it from being used in an indexqual as
> > you'd like.
> >
>
> I verified it by putting a RAISE NOTICE in the function. The fast schema
> runs the function twice (odd, I would think it would run only once). The
> slow schema runs it 30 times (the number of records returned + 1). I know I
> put the functions into both schemas as stable and even dropped and recreated
> the function. Then I verified with EMS Manager and it tells me the DDL for
> the function in the database is set to stable. Is there something I can do
> to tell PostgreSQL that I really did mean stable?
>
maybe this is silly but you can verify what the database thinks of the
function selecting from pg_proc
select pronamespace, provolatile
from pg_proc where proname = 'get_branch_for_zip'
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Re: Sequence vs. Index Scan Jaime Casanova <systemguards@gmail.com>
@ 2007-05-06 18:45 ` Aaron Bono <postgresql@aranya.com>
2007-05-07 10:58 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2007-05-06 18:45 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On 5/5/07, Jaime Casanova <systemguards@gmail.com> wrote:
>
> On 5/5/07, Aaron Bono <postgresql@aranya.com> wrote:
> > On 5/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > "Aaron Bono" <postgresql@aranya.com> writes:
> > > > 9. -> Seq Scan on branch (cost=
> 0.00..4.72
> > rows=1
> > > > width=1281) (actual time= 130129.988..157492.057 rows=1 loops=1)
> > > > 10. Filter: ((start_day
> > <= now()) AND
> > > > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > > > get_branch_for_zip('22151'::character varying)))
> > >
> > > There is something *awfully* wacko about that entry --- the fact that
> > > the cost estimate is less than 5 units means that the planner thinks
> > > there's 4 or fewer pages; either that's way wrong or the
> > > get_branch_for_zip function is taking enormous amounts of time per
> row.
> > > Have you tried timing that function on its own?
> > >
> > > One possible reason for the performance difference is if you have
> > > get_branch_for_zip marked as stable in one database and volatile in
> the
> > > other --- volatile would prevent it from being used in an indexqual as
> > > you'd like.
> > >
> >
> > I verified it by putting a RAISE NOTICE in the function. The fast
> schema
> > runs the function twice (odd, I would think it would run only
> once). The
> > slow schema runs it 30 times (the number of records returned + 1). I
> know I
> > put the functions into both schemas as stable and even dropped and
> recreated
> > the function. Then I verified with EMS Manager and it tells me the DDL
> for
> > the function in the database is set to stable. Is there something I can
> do
> > to tell PostgreSQL that I really did mean stable?
> >
>
> maybe this is silly but you can verify what the database thinks of the
> function selecting from pg_proc
>
> select pronamespace, provolatile
> from pg_proc where proname = 'get_branch_for_zip'
>
select pronamespace, provolatile, proname
from pg_proc where proname = 'get_branch_for_zip';
pronamespace | provolatile | proname
--------------+-------------+--------------------
26644852 | s | get_branch_for_zip
26644856 | s | get_branch_for_zip
The select is using the function on the slow schema as if it were volatile
but as stable on the fast schema.
I did a restart of the service and that didn't help.
Then I inserted 150 more records in the slow schema and pow - it started
working like the fast schema.
So my conclusion is that the function is being treated as volatile even
though it is stable because the number of records is small. Is there any
way to tell PostgreSQL that when I say stable I really mean stable?
I am getting close to a solution. Thanks again for the help!
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Re: Sequence vs. Index Scan Jaime Casanova <systemguards@gmail.com>
2007-05-06 18:45 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
@ 2007-05-07 10:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-07 14:37 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Sullivan @ 2007-05-07 10:58 UTC (permalink / raw)
To: pgsql-sql
On Sun, May 06, 2007 at 01:45:54PM -0500, Aaron Bono wrote:
> Then I inserted 150 more records in the slow schema and pow - it started
> working like the fast schema.
>
> So my conclusion is that the function is being treated as volatile even
> though it is stable because the number of records is small.
I don't think that's the issue. If this is dependent on the
number of records, then for some reason the way the data is
structured means that the planner thinks a seqscan's a better bet.
This is probably due to distribution of the values. You could try
increasing the stats sample, and see if that helps.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Re: Sequence vs. Index Scan Jaime Casanova <systemguards@gmail.com>
2007-05-06 18:45 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-07 10:58 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-05-07 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-07 16:40 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2007-05-07 14:37 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Sun, May 06, 2007 at 01:45:54PM -0500, Aaron Bono wrote:
>> So my conclusion is that the function is being treated as volatile even
>> though it is stable because the number of records is small.
> I don't think that's the issue. If this is dependent on the
> number of records, then for some reason the way the data is
> structured means that the planner thinks a seqscan's a better bet.
> This is probably due to distribution of the values. You could try
> increasing the stats sample, and see if that helps.
It's got nothing to do with distribution, just with numbers of pages to
fetch. You'll nearly always get a seqscan plan if there are only a
couple of pages in the table, simply because it would take more I/O to
read the index too.
The reason this is a problem in this example is that the function is so
expensive to execute. The planner should be avoiding the seqscan on the
basis of CPU cost not I/O cost, but it doesn't know that the function is
expensive enough to drive the decision that way.
In CVS HEAD (8.3-to-be) we've added a "cost" property to functions,
which provides a clean way to fix this issue, but there's no good way to
deal with it in existing releases :-(
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Sequence vs. Index Scan
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 04:25 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Re: Sequence vs. Index Scan Jaime Casanova <systemguards@gmail.com>
2007-05-06 18:45 ` Re: Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-07 10:58 ` Re: Sequence vs. Index Scan Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-07 14:37 ` Re: Sequence vs. Index Scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-07 16:40 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2007-05-07 16:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On 5/7/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrew Sullivan <ajs@crankycanuck.ca> writes:
> > On Sun, May 06, 2007 at 01:45:54PM -0500, Aaron Bono wrote:
> >> So my conclusion is that the function is being treated as volatile even
> >> though it is stable because the number of records is small.
>
> > I don't think that's the issue. If this is dependent on the
> > number of records, then for some reason the way the data is
> > structured means that the planner thinks a seqscan's a better bet.
> > This is probably due to distribution of the values. You could try
> > increasing the stats sample, and see if that helps.
>
> It's got nothing to do with distribution, just with numbers of pages to
> fetch. You'll nearly always get a seqscan plan if there are only a
> couple of pages in the table, simply because it would take more I/O to
> read the index too.
>
> The reason this is a problem in this example is that the function is so
> expensive to execute. The planner should be avoiding the seqscan on the
> basis of CPU cost not I/O cost, but it doesn't know that the function is
> expensive enough to drive the decision that way.
>
> In CVS HEAD (8.3-to-be) we've added a "cost" property to functions,
> which provides a clean way to fix this issue, but there's no good way to
> deal with it in existing releases :-(
>
> regards, tom lane
Since we don't delete records, we just deactivate them, I added 100 dummy
records that are not active. This fixed the problem. As the number of
records in that table grows, I will delete the dummy records.
Thanks for all the help!
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-05-07 16:40 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-05 21:05 Sequence vs. Index Scan Aaron Bono <postgresql@aranya.com>
2007-05-05 21:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-05 22:00 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 00:40 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-06 04:13 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 00:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-06 03:28 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 04:25 ` Aaron Bono <postgresql@aranya.com>
2007-05-06 04:37 ` Jaime Casanova <systemguards@gmail.com>
2007-05-06 18:45 ` Aaron Bono <postgresql@aranya.com>
2007-05-07 10:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-07 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-07 16:40 ` Aaron Bono <postgresql@aranya.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