Hi, Scott and Hale,
Scott Marlowe wrote:
Make sure analyze has been run and that the statistics are fairly
accurate.
It might also help to increase the statistics_target on the column in
question.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. |
Initial testing was with data that essentially looks like a single collection with many items. I then changed this to have 60 collections of 50 items. The result, much better (but not optimum) use of indexs, but a seq scan stillused. Turning seq scan off, all indexes where used. Query was much
On Wed, 2006-08-02 at 07:17, H Hale wrote:
Initial testing was with data that essentially looks like a single collection
with many items.
I then changed this to have 60 collections of 50 items.
The result, much better (but not optimum) use of indexs, but a seq scan still
used.
Turning
Not sure if this helps solve the problem but... (see below) As new records are added Indexes are used for awhile and then at some point postgres switches to seq scan. It is repeatable. Any suggestions/comments to try and solve this are welcome. Thanks Data is as follows:
H Hale wrote:
I am testing a query what that has a sub-select. The query performance is very very poor as shown below due to the use of sequencial scans. The actual row count of both tables is also shown. It appears the row count shown by explain analyze does not match the actual count. Columns
capsa=# explain analyze select name from capsa.flatomfilesysentry
where objectid in ( select dstobj from capsa.flatommemberrelation
where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
QUERY PLAN
Look at that second seq-scan (on flatommemberrelation) - it's looping 5844 times (once for each row in flatmfilesysentry). I'd expect PG to materialise the seq-scan once and then join (unless I'm missing something, the subselect just involves the one test against a constant).I'm guessing something
H Hale [EMAIL PROTECTED] writes:
- Bitmap Heap Scan on flatomfilesysentry (cost=2.00..274.38 rows=3238
width=30) (actual time=0.011..0.013 rows=1 loops=6473)
Recheck Cond: (flatomfilesysentry.objectid = outer.dstobj)
- Bitmap Index Scan on flatomfilesysentry_pkey
Tom, It is unique. Indexes: "flatomfilesysentry_pkey" PRIMARY KEY, btree (objectid) "capsa_flatomfilesysentry_name_idx" btree (name) Foreign-key constraints: "objectid" FOREIGN KEY (objectid) REFERENCES capsa_sys.master(objectid) ON DELETE CASCADE Tom Lane [EMAIL PROTECTED] wrote: H Hale
I am testing a query what that has a sub-select. The query performance is very very poor as shown below due to the use of sequencial scans. The actual row count of both tables is also shown. It appears the row count shown by explain analyze does not match the actual count. Columns dstobj, srcobj
10 matches
Mail list logo
