Thread: slow select
I'm using pg 7.3.4 to do a select involving a join on 2 tables.
The query is taking 15 secs which seems extreme to me considering
the indices that exist on the two tables. EXPLAIN ANALYZE shows
that the indices aren't being used. I've done VACUUM ANALYZE on the
db with no change in results. Shouldn't the indices be used?
Below is what I believe to be the relevant information. I haven't
included the definitions of the tables involved in the foreign
key definititions because I don't think they matter.
Any help will be greatly appreciated.
CREATE TABLE shotpoint (
shot_line_num FLOAT4, \
shotpoint FLOAT4,
x FLOAT4,
y FLOAT4,
template_id INT4,
num_chans INT4)
CREATE TABLE shot_record (
shot_line_num FLOAT4,
shotpoint FLOAT4,
index INT2,
dev INT4,
dev_offset INT8,
bin INT4,
shot_time INT8,
record_length INT4,
nav_x FLOAT4,
nav_y FLOAT4,
num_rus INT4,
status INT4 DEFAULT 0,
reel_num INT4,
file_num INT4,
nav_status INT2,
nav_shot_line FLOAT4,
nav_shotpoint FLOAT4,
nav_depth FLOAT4,
sample_skew INT4,
trace_count INT4,
PRIMARY KEY (shot_line_num, shotpoint, index))
ALTER TABLE shotpoint ADD CONSTRAINT shot_line_fk
FOREIGN KEY (shot_line_num)
REFERENCES shot_line(shot_line_num)
CREATE UNIQUE INDEX shotpoint_idx
ON shotpoint(shot_line_num, shotpoint)
ALTER TABLE shot_record ADD CONSTRAINT shot_record_shotpoint_index_fk
FOREIGN KEY (shot_line_num, shotpoint)
REFERENCES shotpoint(shot_line_num, shotpoint)
EXPLAIN ANALYZE SELECT r.shot_line_num, r.shotpoint, index,
shot_time,
record_length, dev,
dev_offset, num_rus, bin, template_id, trace_count
FROM shot_record r, shotpoint p
WHERE p.shot_line_num = r.shot_line_num
AND p.shotpoint = r.shotpoint;
Merge Join (cost=49902.60..52412.21 rows=100221 width=58) (actual time=12814.28..15000.65 rows=100425 loops=1)
Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint))
-> Sort (cost=13460.90..13711.97 rows=100425 width=46) (actual time=3856.94..4157.01 rows=100425 loops=1)
Sort Key: r.shot_line_num, r.shotpoint
-> Seq Scan on shot_record r (cost=0.00..2663.25 rows=100425 width=46) (actual time=18.00..1089.00
rows=100425loops=1)
-> Sort (cost=36441.70..37166.96 rows=290106 width=12) (actual time=8957.19..9224.09 rows=100749 loops=1)
Sort Key: p.shot_line_num, p.shotpoint
-> Seq Scan on shotpoint p (cost=0.00..5035.06 rows=290106 width=12) (actual time=7.55..2440.06 rows=290106
loops=1)
Total runtime: 15212.05 msec
***********************************************************************
Medora Schauer
Sr. Software Engineer
Fairfield Industries
14100 Southwest Freeway
Suite 600
Sugar Land, Tx 77478-3469
USA
mschauer@fairfield.com
phone: 281-275-7664
fax : 281-275-7551
***********************************************************************
Medora, > I'm using pg 7.3.4 to do a select involving a join on 2 tables. > The query is taking 15 secs which seems extreme to me considering > the indices that exist on the two tables. EXPLAIN ANALYZE shows > that the indices aren't being used. I've done VACUUM ANALYZE on the > db with no change in results. Shouldn't the indices be used? No. You're selecting 100,000 records. For such a large record dump, a seq scan is usually faster. If you don't believe me, try setting enable_seqscan=false and see how long the query takes. -- Josh Berkus Aglio Database Solutions San Francisco
>
> Medora,
>
> > I'm using pg 7.3.4 to do a select involving a join on 2 tables.
> > The query is taking 15 secs which seems extreme to me considering
> > the indices that exist on the two tables. EXPLAIN ANALYZE shows
> > that the indices aren't being used. I've done VACUUM ANALYZE on the
> > db with no change in results. Shouldn't the indices be used?
>
> No. You're selecting 100,000 records. For such a large
> record dump, a seq
> scan is usually faster.
>
> If you don't believe me, try setting enable_seqscan=false and
> see how long the
> query takes.
I did as you suggested (set enable_seqscan = false) and the query now takes 6 sec (vs
15 secs before) :
Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1)
Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint))
-> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11 rows=100425 width=46) (actual
time=24.15..2710.31rows=100425 loops=1)
-> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54 rows=290106 width=12) (actual
time=37.38..1379.64rows=100749 loops=1)
Total runtime: 6086.32 msec
So why did were the indices not used before when they yield a better plan?
Medora, > So why did were the indices not used before when they yield a better plan? Your .conf settings, most likely. I'd lower your random_page_cost and raise your effective_cache_size. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh, > > So why did were the indices not used before when they yield > a better plan? > > Your .conf settings, most likely. I'd lower your > random_page_cost and raise > your effective_cache_size. Increasing effective_cache_size to 10000 did it. The query now takes 4 secs. I left random_page_cost at the default value of 4. I thought, mistakenly apparently, that our database was relatively itty bitty and so haven't messed with the .conf file. Guess I better take a look at all the settings (I know where the docs are). Thanks for your help, Medora *********************************************************************** Medora Schauer Sr. Software Engineer Fairfield Industries ***********************************************************************
Medora, > Increasing effective_cache_size to 10000 did it. That would be 78MB RAM. If you have more than that available, you can increase it further. Ideally, it should be about 2/3 to 3/4 of available RAM. >The query now > takes 4 secs. I left random_page_cost at the default value of 4. > I thought, mistakenly apparently, that our database was relatively > itty bitty and so haven't messed with the .conf file. Actually, for a itty bitty database on a fast machine, you definitely want to lower random_page_cost. It's a large database that would make you cautious about this. -- -Josh Berkus Aglio Database Solutions San Francisco
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes: JB> Medora, >> Increasing effective_cache_size to 10000 did it. JB> That would be 78MB RAM. If you have more than that available, you can JB> increase it further. Ideally, it should be about 2/3 to 3/4 of available JB> RAM. Assuming your OS will use that much RAM for the cache... the whole world's not Linux :-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
"Medora Schauer" <mschauer@fairfield.com> writes:
> Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1)
> Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint))
> -> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11 rows=100425 width=46) (actual
time=24.15..2710.31rows=100425 loops=1)
> -> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54 rows=290106 width=12) (actual
time=37.38..1379.64rows=100749 loops=1)
> Total runtime: 6086.32 msec
>
> So why did were the indices not used before when they yield a better plan?
There's another reason. Notice it thinks the second table will return 290k
records. In fact it only returns 100k records. So it's optimizing on the
assumption that it will have to read 3x as many records as it actually will...
I'm not clear if there's anything you can do to improve this estimate though.
--
greg
Vivek, > Assuming your OS will use that much RAM for the cache... the whole > world's not Linux :-) It's not? Darn! Actually, what OS's can't use all idle ram for kernel cache? I should note that in my performance docs .... -- Josh Berkus Aglio Database Solutions San Francisco
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes: JB> Vivek, >> Assuming your OS will use that much RAM for the cache... the whole >> world's not Linux :-) JB> It's not? Darn! :-) JB> Actually, what OS's can't use all idle ram for kernel cache? I JB> should note that in my performance docs .... FreeBSD. Limited by the value of "sysctl vfs.hibufspace" from what I understand. This value is set at boot based on available RAM and some other tuning parameters.
Vivek Khera wrote: >>>>>>"JB" == Josh Berkus <josh@agliodbs.com> writes: > JB> Actually, what OS's can't use all idle ram for kernel cache? I > JB> should note that in my performance docs .... > > FreeBSD. Limited by the value of "sysctl vfs.hibufspace" from what I > understand. This value is set at boot based on available RAM and some > other tuning parameters. Actually I wanted to ask this question for long time. Can we have guidelines about how to set effective cache size for various OSs? Linux is pretty simple. Everything free is buffer cache. FreeBSD, not so straightforward but there is a sysctl.. How about HP-UX, Solaris and AIX? Other BSDs? and most importantly windows? That could add much value to the tuning guide. Isn't it? Shridhar