Ravi Krishna <sravikrishna3@gmail.com> writes:
> So cost wise they both look same, still when i run the sql in a loop
> in large numbers, it takes rougly 1.8 to 2 times more than non
> partitioned table.
If you're testing cases that only involve fetching a single row,
the discrepancy could well be down to extra planning time. Proving
that the other partitions don't need to be scanned is far from free.
It's also worth realizing that for queries that fetch just one or
a few rows, it's very unlikely that partitioning can beat an unpartitioned
table, period. Basically, partitioning replaces a runtime search of the
top levels of a large index with a plan-time proof that other partitions
need not be visited. That is not going to be cheaper and could well be a
lot more expensive.
The situations where partitioning is useful boil down to:
1. You have repetitive, stylized requirements such as "every month,
delete all data older than X months" that can be mapped to "drop
the oldest partition" instead of doing an expensive table scan.
2. You can arrange things so that certain partitions are accessed
far more often than others, thus directing most disk traffic to
specific child tables that will remain in RAM cache most of the time.
(In principle, you could get similar cache-friendly behavior from a
clustered unpartitioned table, but it's usually too hard to ensure
that such a table stays clustered.)
It does not sound like your test case is exercising either of those
win scenarios, and all you're measuring is the overhead of partitioning,
which as I said is substantial.
regards, tom lane
