Thread: Query Time

Query Time

From
"Lufkin, Brad"
Date:
I'm running the following query:

    SELECT * FROM someTable LIMIT 20

I turned on explain analyze (tip #8!) and was told that the query plan was
sequential (no surprise there) with an estimated cost of between 0.00 and
1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half
minutes). What's going on?

Re: Query Time

From
"scott.marlowe"
Date:
On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> I'm running the following query:
>
>     SELECT * FROM someTable LIMIT 20
>
> I turned on explain analyze (tip #8!) and was told that the query plan was
> sequential (no surprise there) with an estimated cost of between 0.00 and
> 1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half
> minutes). What's going on?

costs are estimated as a percentage of a cost of a single page access in
sequential mode.  I.e. a single sequential page access is assumed to cost
1.0, and everything is relative to that.  The cost numbers do NOT
translate directly into any time unit.



Re: Query Time

From
"Lufkin, Brad"
Date:
Thanks for the info. Still, I'm surprised that a limited query would take 90
seconds.

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, August 13, 2003 4:20 PM
To: Lufkin, Brad
Cc: 'psql'
Subject: Re: [JDBC] Query Time


On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> I'm running the following query:
>
>     SELECT * FROM someTable LIMIT 20
>
> I turned on explain analyze (tip #8!) and was told that the query plan was
> sequential (no surprise there) with an estimated cost of between 0.00 and
> 1.07. Surprisingly, the actual time was around 90000 msec (or
one-and-a-half
> minutes). What's going on?

costs are estimated as a percentage of a cost of a single page access in
sequential mode.  I.e. a single sequential page access is assumed to cost
1.0, and everything is relative to that.  The cost numbers do NOT
translate directly into any time unit.


Re: Query Time

From
Tom Lane
Date:
"Lufkin, Brad" <brad.lufkin@ngc.com> writes:
> I'm running the following query:
>     SELECT * FROM someTable LIMIT 20

> I turned on explain analyze (tip #8!) and was told that the query plan was
> sequential (no surprise there) with an estimated cost of between 0.00 and
> 1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half
> minutes). What's going on?

Lots and lots and lots of dead tuples, perhaps?  How often do you vacuum
this table?  What does VACUUM VERBOSE have to say about it?

            regards, tom lane

Re: Query Time

From
"scott.marlowe"
Date:
How many rows in that table?  90 seconds is a long time for a select to
take, period, with or without a limit.

On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> Thanks for the info. Still, I'm surprised that a limited query would take 90
> seconds.
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:20 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > I'm running the following query:
> >
> >     SELECT * FROM someTable LIMIT 20
> >
> > I turned on explain analyze (tip #8!) and was told that the query plan was
> > sequential (no surprise there) with an estimated cost of between 0.00 and
> > 1.07. Surprisingly, the actual time was around 90000 msec (or
> one-and-a-half
> > minutes). What's going on?
>
> costs are estimated as a percentage of a cost of a single page access in
> sequential mode.  I.e. a single sequential page access is assumed to cost
> 1.0, and everything is relative to that.  The cost numbers do NOT
> translate directly into any time unit.
>
>


Re: Query Time

From
"Lufkin, Brad"
Date:
30 million rows....

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, August 13, 2003 4:39 PM
To: Lufkin, Brad
Cc: 'psql'
Subject: Re: [JDBC] Query Time


How many rows in that table?  90 seconds is a long time for a select to
take, period, with or without a limit.

On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> Thanks for the info. Still, I'm surprised that a limited query would take
90
> seconds.
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:20 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > I'm running the following query:
> >
> >     SELECT * FROM someTable LIMIT 20
> >
> > I turned on explain analyze (tip #8!) and was told that the query plan
was
> > sequential (no surprise there) with an estimated cost of between 0.00
and
> > 1.07. Surprisingly, the actual time was around 90000 msec (or
> one-and-a-half
> > minutes). What's going on?
>
> costs are estimated as a percentage of a cost of a single page access in
> sequential mode.  I.e. a single sequential page access is assumed to cost
> 1.0, and everything is relative to that.  The cost numbers do NOT
> translate directly into any time unit.
>
>


---------------------------(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

Re: Query Time

From
"scott.marlowe"
Date:
I'd aree with Tom here that it sounds like there's lots of dead tuples in
that table.

On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> 30 million rows....
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:39 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> How many rows in that table?  90 seconds is a long time for a select to
> take, period, with or without a limit.
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > Thanks for the info. Still, I'm surprised that a limited query would take
> 90
> > seconds.
> >
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > Sent: Wednesday, August 13, 2003 4:20 PM
> > To: Lufkin, Brad
> > Cc: 'psql'
> > Subject: Re: [JDBC] Query Time
> >
> >
> > On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> >
> > > I'm running the following query:
> > >
> > >     SELECT * FROM someTable LIMIT 20
> > >
> > > I turned on explain analyze (tip #8!) and was told that the query plan
> was
> > > sequential (no surprise there) with an estimated cost of between 0.00
> and
> > > 1.07. Surprisingly, the actual time was around 90000 msec (or
> > one-and-a-half
> > > minutes). What's going on?
> >
> > costs are estimated as a percentage of a cost of a single page access in
> > sequential mode.  I.e. a single sequential page access is assumed to cost
> > 1.0, and everything is relative to that.  The cost numbers do NOT
> > translate directly into any time unit.
> >
> >
>
>
> ---------------------------(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


Re: Query Time

From
Jean-Luc Lachance
Date:

Irrelevant of the number of rows, PG should read 8 tuples and return.

There may be alot of deleted tupples at the head of that table.
When was the last time you vaccumed?


"Lufkin, Brad" wrote:
>
> 30 million rows....
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:39 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
> How many rows in that table?  90 seconds is a long time for a select to
> take, period, with or without a limit.
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > Thanks for the info. Still, I'm surprised that a limited query would take
> 90
> > seconds.
> >
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > Sent: Wednesday, August 13, 2003 4:20 PM
> > To: Lufkin, Brad
> > Cc: 'psql'
> > Subject: Re: [JDBC] Query Time
> >
> >
> > On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> >
> > > I'm running the following query:
> > >
> > >     SELECT * FROM someTable LIMIT 20
> > >
> > > I turned on explain analyze (tip #8!) and was told that the query plan
> was
> > > sequential (no surprise there) with an estimated cost of between 0.00
> and
> > > 1.07. Surprisingly, the actual time was around 90000 msec (or
> > one-and-a-half
> > > minutes). What's going on?
> >
> > costs are estimated as a percentage of a cost of a single page access in
> > sequential mode.  I.e. a single sequential page access is assumed to cost
> > 1.0, and everything is relative to that.  The cost numbers do NOT
> > translate directly into any time unit.
> >
> >
>
> ---------------------------(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Re: Query Time

From
"Lufkin, Brad"
Date:
Is there some objective way to confirm this? (Sorry for what may seem like
obvious questions, but I'm a newbie with psql--less than one week!)

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, August 13, 2003 5:03 PM
To: Lufkin, Brad
Cc: 'psql'
Subject: Re: [JDBC] Query Time


I'd aree with Tom here that it sounds like there's lots of dead tuples in
that table.

On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> 30 million rows....
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:39 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> How many rows in that table?  90 seconds is a long time for a select to
> take, period, with or without a limit.
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > Thanks for the info. Still, I'm surprised that a limited query would
take
> 90
> > seconds.
> >
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > Sent: Wednesday, August 13, 2003 4:20 PM
> > To: Lufkin, Brad
> > Cc: 'psql'
> > Subject: Re: [JDBC] Query Time
> >
> >
> > On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> >
> > > I'm running the following query:
> > >
> > >     SELECT * FROM someTable LIMIT 20
> > >
> > > I turned on explain analyze (tip #8!) and was told that the query plan
> was
> > > sequential (no surprise there) with an estimated cost of between 0.00
> and
> > > 1.07. Surprisingly, the actual time was around 90000 msec (or
> > one-and-a-half
> > > minutes). What's going on?
> >
> > costs are estimated as a percentage of a cost of a single page access in

> > sequential mode.  I.e. a single sequential page access is assumed to
cost
> > 1.0, and everything is relative to that.  The cost numbers do NOT
> > translate directly into any time unit.
> >
> >
>
>
> ---------------------------(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Re: Query Time

From
"scott.marlowe"
Date:
vacuum verbose;

should tell you something.

On Wed, 13 Aug 2003, Lufkin, Brad wrote:

> Is there some objective way to confirm this? (Sorry for what may seem like
> obvious questions, but I'm a newbie with psql--less than one week!)
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 5:03 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> I'd aree with Tom here that it sounds like there's lots of dead tuples in
> that table.
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > 30 million rows....
> >
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > Sent: Wednesday, August 13, 2003 4:39 PM
> > To: Lufkin, Brad
> > Cc: 'psql'
> > Subject: Re: [JDBC] Query Time
> >
> >
> > How many rows in that table?  90 seconds is a long time for a select to
> > take, period, with or without a limit.
> >
> > On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> >
> > > Thanks for the info. Still, I'm surprised that a limited query would
> take
> > 90
> > > seconds.
> > >
> > > -----Original Message-----
> > > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > > Sent: Wednesday, August 13, 2003 4:20 PM
> > > To: Lufkin, Brad
> > > Cc: 'psql'
> > > Subject: Re: [JDBC] Query Time
> > >
> > >
> > > On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> > >
> > > > I'm running the following query:
> > > >
> > > >     SELECT * FROM someTable LIMIT 20
> > > >
> > > > I turned on explain analyze (tip #8!) and was told that the query plan
> > was
> > > > sequential (no surprise there) with an estimated cost of between 0.00
> > and
> > > > 1.07. Surprisingly, the actual time was around 90000 msec (or
> > > one-and-a-half
> > > > minutes). What's going on?
> > >
> > > costs are estimated as a percentage of a cost of a single page access in
>
> > > sequential mode.  I.e. a single sequential page access is assumed to
> cost
> > > 1.0, and everything is relative to that.  The cost numbers do NOT
> > > translate directly into any time unit.
> > >
> > >
> >
> >
> > ---------------------------(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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>