Hi,
I have a view that has something like this: select x, y, z from tbl order by x, y
I have created a special index on x + y
I have run analyze
Still, when I use explain, pg says it will first sort my tables instead of using my index
How is that possible ?
When I do explain select x,y,z
On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote:
Still, when I use explain, pg says it will first sort my tables instead
of using my index
How is that possible ?
Can we see the output of the explain analyze?
The definition of the view?
Regards
Russell Smith
rvponp=# explain select * from vw_document_pagesperjob ;
QUERY PLAN
Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706)
-> Sort
Please CC the list.
On Mon, 13 Jun 2005 05:11 pm, Yves Vindevogel wrote:
create or replace view vw_document_pagesperjob as
select documentname, eventdate, eventtime, loginuser,
fnFormatInt(pages) as pages
from tblPrintjobs
order by descpages, documentname ;
rvponp=# explain select
Note the last query below (prev post)
There it does use the index
rvponp=# create type tpJobsPerDay as
rvponp-# ( documentname varchar(1000),
rvponp(# eventdate date,
rvponp(# eventtime time,
rvponp(# loginuser varchar(255),
rvponp(# pages varchar(20)
rvponp(# ) ;
CREATE TYPE
rvponp=# create
rvponp=# vacuum verbose tblPrintjobs ;
INFO: vacuuming public.tblprintjobs
INFO: index pkprintjobs now contains 622972 row versions in 8410 pages
DETAIL: 9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.60s/0.31u sec elapsed 31.68 sec.
INFO:
Apologies - I should have said output of 'VACUUM VERBOSE mytable'.
(been using 8.1, which displays dead tuple info in ANALYZE...).
Mark
Yves Vindevogel wrote:
rvponp=# analyze verbose tblPrintjobs ;
INFO: analyzing public.tblprintjobs
INFO: tblprintjobs: 19076 pages, 3000 rows sampled, 588209
I have started this on my testmachine at 11h20. It's still running and here it's 13h40.
Setup:
Intel P4 2Ghz, 1 Gb ram
ReiserFS 3 (with atime in fstab, which is not optimal)
Slackware 10
PG 7.4
I have the same problems on my OSX and other test machines.
It's frustrating. Even Microsoft Access
What else I don't understand is that an update is so slow, whereas this
rvponp=# insert into tblTest (id, descpages) select oid, -pages from tblPrintjobs ;
INSERT 0 622972
rvponp=# delete from tblTest ;
DELETE 622972
rvponp=#
takes about 1 minute for the insert, and 5 seconds for the delete.
We have two index's like so
l1_historical=# \d N_intra_time_idx
Index N_intra_time_idx
Column |Type
+-
time | timestamp without time zone
btree
l1_historical=# \d N_intra_pkey
Index N_intra_pkey
Column |Type
Hi there
I have a query (please refer to
http://213.173.234.215:8080/get_content_plan.htm for the query as well
as query plan) that is slow when it's run the first time and fast(ish)
on all successive runs within a reasonable time period.
That is, if the query is not run for like 30 min,
Oh, we are running 7.4.2 btw. And our random_page_cost = 1
On 13 Jun 2005, at 14:02, Alex Stapleton wrote:
We have two index's like so
l1_historical=# \d N_intra_time_idx
Index N_intra_time_idx
Column |Type
+-
time | timestamp without
On Mon, Jun 13, 2005 at 00:29:08 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
As you probably saw in my last reply, I went back to the old index and
tried the query you and Tom Lane
I've done a lot of work with a bookkeeping system where we have such
redundancy built in. The auditors, however, need to be able to generate
lists of the financial transaction detail to support balances. These
reports are among the most demanding in the system. I shudder to think
how
Yves Vindevogel [EMAIL PROTECTED] writes:
rvponp=# explain select * from vw_document_pagesperjob limit 10 ;
QUERY PLAN
--
Limit (cost=82796.59..82796.72
It sure would be nice if the optimizer would consider that it had the
leeway to add any column which was restricted to a single value to any
point in the ORDER BY clause. Without that, the application programmer
has to know what indexes are on the table, rather than being able to
just worry about
Yves Vindevogel [EMAIL PROTECTED] writes:
rvponp=3D# vacuum verbose tblPrintjobs ;
INFO: vacuuming public.tblprintjobs
[ twenty-one different indexes on one table ]
Well, there's your problem. You think updating all those indexes is
free? It's *expensive*. Heed the manual's advice: avoid
Jona [EMAIL PROTECTED] writes:
I have a query (please refer to
http://213.173.234.215:8080/get_content_plan.htm for the query as well
as query plan) that is slow when it's run the first time and fast(ish)
on all successive runs within a reasonable time period.
This leads me to suspect
Alex Stapleton [EMAIL PROTECTED] writes:
l1_historical=# \d N_intra_pkey
Index N_intra_pkey
Column |Type
+-
symbol | text
time | timestamp without time zone
unique btree (primary key)
and on queries like this
select * from
Kevin Grittner wrote:
It sure would be nice if the optimizer would consider that it had the
leeway to add any column which was restricted to a single value to any
point in the ORDER BY clause. Without that, the application programmer
has to know what indexes are on the table, rather than being
Alex Stapleton wrote:
Oh, we are running 7.4.2 btw. And our random_page_cost = 1
Which is only correct if your entire db fits into memory. Also, try
updating to a later 7.4 version if at all possible.
On 13 Jun 2005, at 14:02, Alex Stapleton wrote:
We have two index's like so
On 13 Jun 2005, at 15:47, John A Meinel wrote:
Alex Stapleton wrote:
Oh, we are running 7.4.2 btw. And our random_page_cost = 1
Which is only correct if your entire db fits into memory. Also, try
updating to a later 7.4 version if at all possible.
I am aware of this, I didn't
Tom Lane wrote:
This was just covered in excruciating detail yesterday ...
You need to write
order by symbol desc, time desc limit 1
to get the planner to recognize the connection to the sort order
of this index. Since you're only selecting one value of symbol,
the actual output
On Mon, Jun 13, 2005 at 09:51:57 -0500,
John A Meinel [EMAIL PROTECTED] wrote:
I don't know if there are specific reasons why not, other than just not
being implemented yet. It might be tricky to get it correct (for
instance, how do you know which columns can be added, which ones will be
I forgot cc
Begin forwarded message:
From: Yves Vindevogel [EMAIL PROTECTED]>
Date: Mon 13 Jun 2005 17:45:19 CEST
To: Tom Lane [EMAIL PROTECTED]>
Subject: Re: [PERFORM] Updates on large tables are extremely slow
Yes, but if I update one column, why should PG update 21 indexes ?
There's only
Yves Vindevogel wrote:
I forgot cc
Begin forwarded message:
From: Yves Vindevogel [EMAIL PROTECTED]
Date: Mon 13 Jun 2005 17:45:19 CEST
To: Tom Lane [EMAIL PROTECTED]
Subject: Re: [PERFORM] Updates on large tables are extremely slow
Yes, but if I update one column, why should PG update 21
John A Meinel [EMAIL PROTECTED] wrote:
I don't know if there are specific reasons why not, other than just not
being implemented yet. It might be tricky to get it correct
Not so much tricky to get correct, as potentially expensive to test for;
it'd be quite easy to waste a lot of cycles
Ok, if all 21 are affected, I can understand the problem.
But allow me to say that this is a functional error
On 13 Jun 2005, at 18:02, Richard Huxton wrote:
Yves Vindevogel wrote:
I forgot cc
Begin forwarded message:
From: Yves Vindevogel [EMAIL PROTECTED]>
Date: Mon 13 Jun 2005 17:45:19 CEST
On Mon, Jun 13, 2005 at 12:22:14 -0400,
Tom Lane [EMAIL PROTECTED] wrote:
I don't think the use-case has been shown that justifies doing this much
work to ignore useless ORDER BY clauses. The examples that have come up
in the past all suggest ignoring index columns not the other way
I agree that ignoring useless columns in an ORDER BY clause is less
important than ignoring index columns where the value is fixed. There
is one use case for ignoring useless ORDER BY columns that leaps to
mind, however -- a column is added to the ORDER BY clause of a query to
help out the
On Mon, Jun 13, 2005 at 11:46:46 -0500,
Kevin Grittner [EMAIL PROTECTED] wrote:
I agree that ignoring useless columns in an ORDER BY clause is less
important than ignoring index columns where the value is fixed. There
is one use case for ignoring useless ORDER BY columns that leaps to
mind,
Ok, if all 21 are affected, I can understand the problem.
But allow me to say that this is a functional error
It's a choice between total throughput on a high load, high connection
basis (MVCC dramatically wins here), versus a single user, low load
scenario (MS Access is designed for this).
Thank you for the response Tom, I bet you get a lot of mails with
"trivial" solutions (mine likely being one of them)
I for one however truly appreciate you taking the time to answer them.
Run the query more often?
The query is dynamically constructed from user input, although the
I just dropped 19 of the 21 indexes. I just left the primary key constraint and my unique index on 3 fields ...
I did a vacuum full and an analyse I just ran the query again some 20 minutes ago.
Guess what It's still running !!
So it's not that much faster for the moment.
I just want
Hi,
At 19:22 13/06/2005, Yves Vindevogel wrote:
It can't be indexes on other tables, right ?
It could be foreign keys from that table referencing other tables or
foreign keys from other tables referencing that table, especially if you
don't have the matching indexes...
Jacques.
Bruno Wolff III wrote:
I am guessing you mean 'file_type' instead of 'file_info'.
To do this efficiently you want an index on (file_type, file_parent_dir,
file_name). Currently you only have an index on (file_parent_dir, file_name)
which won't help for this query. You also need to order by
Kevin Grittner wrote:
tle-bu= \d file_info_7_display_idx
Index public.file_info_7_display_idx
Column | Type
-+--
file_type | character varying(2)
file_parent_dir | text
file_name | text
btree, for table public.file_info_7
Madison Kelly [EMAIL PROTECTED] writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
that the planner thinks this is faster
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
that the
Hi All,
We are looking to upgrade to 8.0 from 7.3.2 on production server. The currentproduction systemwe are using is
2 x 2.4 Ghz Intel Xeon CPU with HT(4 virtual CPUs)
RAM - 1GB
HDD - 34GB SCSI
-
Production DB size:
Saranya Sivakumar wrote:
Hi All,
We are looking to upgrade to 8.0 from 7.3.2 on production server. The
current production system we are using is
2 x 2.4 Ghz Intel Xeon CPU with HT(4 virtual CPUs)
RAM - 1GB
HDD - 34GB SCSI
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu= set enable_seqscan = off; SET
tle-bu= EXPLAIN ANALYZE SELECT
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need to
figure out how to tell the planner how to use it...
Be careful extrapolating too much from a single query in a single context.
Notably you might want to test the same query after
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Simple division shows that the planner's cost estimate ratio between the
seqscan and the indexscan (11956.84 vs 83171.78) is
Bruno Wolff III wrote:
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu= set enable_seqscan = off; SET
tle-bu= EXPLAIN
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Simple division shows that the planner's cost estimate ratio between the
seqscan and the indexscan (11956.84
Elein,
I've got a list of old resource requirements.
I want to know how far off they are and if anything
crucial is missing. My usual recommendation is
as much as you can afford so I don't usually deal
with real numbers :)
These look very approximate.
RAM:
Number of connections
Ok, if all 21 are affected, I can understand the problem.
But allow me to say that this is a functional error
No, it's normal MVCC design...
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
48 matches
Mail list logo
