pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange behavior of child table.
2+ messages / 2 participants
[nested] [flat]
* Strange behavior of child table.
@ 2011-06-01 06:07 Jenish <jenishvyas@gmail.com>
2011-06-01 10:23 ` Re: Strange behavior of child table. Greg Smith <greg@2ndQuadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jenish @ 2011-06-01 06:07 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I have created partition on table Round_Action , which has 3 inherited
partition tables.
When I am firing a simple select query with limit on parent table it is
taking huge time to execute. But when I am firing this query directly on
inherited table it is taking few milliseconds.
EXP.
select * from Round_Action where action_id =50000 limit 100 → execution time
80 sec
select * from Round_Action_CH1 action_id =50000 limit 100 → execution time
0.1 sec
Round_Action is the parent table and has no record in the tables, all the
records are lying in child tables.
Table is having index on action_id.
Partition is trigger based.
Postgres Version : (PostgreSQL) 8.4.6
Why there is difference in execution time? What I am doing wrong?
--
Thanks & regards,
JENISH VYAS
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange behavior of child table.
2011-06-01 06:07 Strange behavior of child table. Jenish <jenishvyas@gmail.com>
@ 2011-06-01 10:23 ` Greg Smith <greg@2ndQuadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Smith @ 2011-06-01 10:23 UTC (permalink / raw)
To: pgsql-admin
On 06/01/2011 02:07 AM, Jenish wrote:
> select * from Round_Action where action_id =50000 limit 100 →
> execution time 80 sec
>
> select * from Round_Action_CH1 action_id =50000 limit 100 → execution
> time 0.1 sec
>
>
First off: each of the child tables needs to have the index created on
them. That doesn't get inherited just by putting it on the master.
If you already did that, try running these both with "EXPLAIN". Sharing
the two query plans here would help figure out what's happening.
Showing the definition of the index on one of the child tables would be
helpful too.
There are some optimizer limitations in PostgreSQL versions before 9.0
that prevent it from using an index on the child tables in some
situations where people expect it to, which includes aggregates like
MIN/MIN. I'm not sure if your LIMIT case is running into the same
issue, the plan will help confirm what's going on.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-06-01 10:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-01 06:07 Strange behavior of child table. Jenish <jenishvyas@gmail.com>
2011-06-01 10:23 ` Greg Smith <greg@2ndQuadrant.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